VBA for adding command button and assigning macro

[Ody]

Whats the VBA for adding a command button and assigning a macro to it?

For example, I have a macro that adds new sheets to my workbook. I'd like to incorporate into that macro the step of adding a command button to the new sheet and assigning a macro to that button which hides the sheet.

I know the VBA for hiding sheets but I can't figure out how to add the button and assign the macro to it.

Thanks for any help!

 

[CWatts]

Whenever you're stuck with some code, it's always easiest to just use the built in macro recorder in Excel and then see what you get for code.

it's pretty straight forward, just put the screen coordinates for where to draw the button and what to set the action to and then select a worksheet cell so that the button is no longer selected in edit mode.

Sub AddButton()
    ActiveSheet.Buttons.Add(251.25, 39.75, 124.5, 42.75).Select
    Selection.OnAction = "StupidSub"
    Range("A1").Select
End Sub

 

[Smitty]

You can also do it without selecting:

ActiveSheet.Buttons.Add(251.25, 39.75, 124.5, 42.75).OnAction = "StupidSub"

 

[Ody]

Thanks for the info but when I add the code:

ActiveSheet.Buttons.Add(791.25, 6, 60.75, 15).OnAction = "Summary"

The button appears with the next sequential number for the control, i.e Button 18, Button 19, etc... and not titled "Summary".

Also, I'm not sure how I would create and assign a macro to the button that would hide the sheet. Any suggestions?

Thanks!

 

[CWatts]

The OnAction piece is the macro name, not the title of the button.

 

[Ody]

I see, thanks.