Option Explicit 
 
Sub AddButtonToActiveSheet() 
     
    Dim MyCmdBtn As OLEObject, N%, MySheet$ 
     
    Application.ScreenUpdating = False 
     
     '<< ADD A COMMAND BUTTON TO THE ACTIVE SHEET >>
    Set MyCmdBtn = ActiveSheet.OLEObjects. _ 
    Add(ClassType:="Forms.CommandButton.1", Link:=False, _ 
    DisplayAsIcon:=False, Left:=50, Top:=50, _ 
    Width:=200, Height:=30) 
     
     '<< FORMAT THE BUTTON HOWEVER YOU WANT IT - EXAMPLE BELOW >>
    With MyCmdBtn 
        .Name = "MyButton" 
        .Object.FontSize = 12 
        .Object.FontBold = True 
        .Object.FontItalic = True 
        .Object.Caption = "Click here - it turns me on..." 
    End With 
     
     '<< ADD CODE FOR THIS BUTTON TO THE RELEVANT WORKSHEET MODULE >>
     '//(add your own code to do whatever you want below - this is a demo that
     '//shows 2 message boxes then deletes the button and runs code to delete
     '//all the code {including the Sub RemoveCode} i.e. It deletes itself)
    With ThisWorkbook.VBProject.VBComponents(ActiveSheet. _ 
        CodeName).CodeModule 
        N = .CountOfLines 
        .InsertLines N + 1, "Private Sub MyButton_Click()" 
        .InsertLines N + 2, vbNewLine 
        .InsertLines N + 3, vbTab & "MsgBox " & """" & "Insert your own code" & """" 
        .InsertLines N + 4, vbTab & "MsgBox " & """" & "Demonstration Finished " & _ 
        "- Removing Button and Code" & """" & _ 
        ", ," & """" & "Removing Button..." & """" 
        .InsertLines N + 5, vbTab & "ActiveSheet.Shapes(""MyButton"").Cut" 
        .InsertLines N + 6, vbTab & "RemoveCode" 
        .InsertLines N + 7, vbNewLine 
        .InsertLines N + 8, "End Sub" 
        .InsertLines N + 9, vbNewLine 
         
         '<< NOW REMOVE ALL THE ADDED CODE (BUT LEAVE ANY PRE-EXISTING CODE) >>
        .InsertLines N + 10, "Private Sub RemoveCode()" 
        .InsertLines N + 11, "Dim I%, N%" 
        .InsertLines N + 12, vbTab & "With ThisWorkbook.VBProject." & _ 
        "VBComponents(ActiveSheet.CodeName).CodeModule " 
        .InsertLines N + 13, vbTab & "N = 22" 
        .InsertLines N + 14, vbTab & vbTab & "Do Until N = -1" 
        .InsertLines N + 15, vbTab & vbTab & "I = .CountOfLines - N" 
        .InsertLines N + 16, vbTab & vbTab & "ThisWorkbook.VBProject." & _ 
        "VBComponents(ActiveSheet.CodeName)." & _ 
        "CodeModule.DeleteLines I" 
        .InsertLines N + 17, vbTab & vbTab & "N = N - 1" 
        .InsertLines N + 18, vbTab & vbTab & "Loop" 
        .InsertLines N + 19, vbTab & "End With" 
        .InsertLines N + 20, "End Sub" 
    End With 
End Sub