Create command button with VBA

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Commandbutton.1", Link:=True _
, DisplayAsIcon:=False, Left:=6, Top:=91, Width:=77, Height:=33). _
Select

This code will put the button on the active sheet. You would then have to create code for the onclick event for the button on the worksheet that it was being placed on.

Not sure if you can assign a macro without putting the code on the worksheet but this should get you started.

DaK

Thank you DaK. Your solution did not work. However, in trying to figure out the details of what was actually happening, I had a moment of clarity. All I need to do was record the darn macro and see what it came up with. After doing that and fiddling with the code I found the solution and it was suprisingly easy. Here it is:

Sub CreateButton()
ActiveSheet.Buttons.Add(199.5, 20, 81, 36).Select
Selection.Name = "New Button"
Selection.OnAction = "CheckTotals"
ActiveSheet.Shapes("New Button").Select
Selection.Characters.Text = "Check Totals"
End Sub

Dave S.

Strange it didn't work as I recorded this macro as well about 2 months ago for a project I was doing. Oh well, at least it's solved.

DaK

DaK,

I didn't mean that it didn't work. It created the command button, but like you said, I couldn't figure out how to do anything with it (I couldn't even delete it!). Still, it was the inspiration for my solution so thanks again.

Dave

Thanks Dave