VBA/Excel/Access/Word/Data Type/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.rumandButton.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