Generating a new worksheet, built-in button, and event handler : OLE Objects « Data Type « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Data Type
3. OLE Objects

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

 

Related examples in the same category