Excel 2007 VBA to add a button

Question

I am trying to write some codes to place a button into my worksheet automatically and when click on the button it will execute some codes. I wrote my code based on the example I saw from the book "Excel 2013 Power Programming with VBA" (p896). I am using Excel 2007 though not sure if it matters. Anyways, I modified the codes to below:

*Sub AddButtonAndCode()  
Dim NewButton As OLEObject  
Dim NewSheet As Worksheet  
Set NewSheet = Worksheets("Sheet1")  
Set NewButton = NewSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=5, Top:=5, Height:=25, Width:=100)  
Code = "Sub CommandButton1_Click()" & vbCrLf  
Code = Code & "  MsgBox ""???""" & vbCrLf  
Code = Code & "End Sub"
With ActiveWorkbook.VBProject.VBComponents(NewSheet.Name).CodeModule
    NextLine = .CountOfLines + 1
    .InsertLines NextLine, Code
End With
End Sub*

It works for the button part, but after I added the parts below defining the button, it gives me error "Subscript out of range"

Could anybody help me to figure out what's wrong in there?

Thank you very much!

Additional questions: How do I turn this into an add-in so that I can run it on any workbook to place the button? — Huibear, Nov 13 '15 at 16:21

score 1 · Accepted Answer · answered Nov 12 '15 at 23:43

Your code works for me with a couple of tweaks.

Sub AddButtonAndCode()

    Dim NewButton As OLEObject
    Dim NewSheet As Worksheet, Code As String
    Dim nextline As Long

    Set NewSheet = Worksheets("Sheet1")

    Set NewButton = NewSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                       Left:=5, Top:=5, Height:=25, Width:=100)

    NewButton.Name = "CommandButton1"  '<<<<<<<<< ensure correct name

    Code = "Sub CommandButton1_Click()" & vbCrLf
    Code = Code & "  MsgBox ""???""" & vbCrLf
    Code = Code & "End Sub"

    'Use NewSheet.CodeName not NewSheet.Name
    With ActiveWorkbook.VBProject.VBComponents(NewSheet.CodeName).CodeModule
        nextline = .CountOfLines + 1
        .InsertLines nextline, Code
    End With

End Sub

In a fresh workbook a sheet's Name and CodeName will be the same, so your original line would work in that case, but would then fail if either the sheet or its code module was renamed.

It works! Thanks a lot! Now I am gonna go expand the code part....hope it would still work.... — Huibear, Nov 13 '15 at 15:11

Excel 2007 VBA to add a button

1 Answers1