Mega Code Archive

 

Categories / VisualBasic Script / Data Type

 

Generating a new worksheet, built-in button, and event handler

Sub AddSheetAndButton()     Dim NewSheet As Worksheet     Dim NewButton As OLEObject     Set NewSheet = Sheets.Add     Set NewButton = NewSheet.OLEObjects.Add("Forms.CommandButton.1")     With NewButton         .Left = 4         .Top = 4         .Width = 100         .Height = 24         .Object.Caption = "Return to Sheet1"     End With     Code = "Sub CommandButton1_Click()" & vbCrLf     Code = Code & "      MsgBox ""Sheet1.""" & vbCrLf     Code = Code & "End Sub"          With ThisWorkbook.VBProject. _       VBComponents(NewSheet.name).CodeModule         NextLine = .CountOfLines + 1         .InsertLines NextLine, Code     End With End Sub