Macro to Create Button and assign code

Good morning gang. I am trying to write a macro that will create a command button on a sheet and assign VBA code to that button. Basically, my original code creates a new sheet. I would like that new sheet to have button on it that will, when clicked, run another macro.

Here's what I have (of course it doesn't work as intended. The button is created, but the label isn't changed nor is the code assigned):

Please Login or Register  to view this content.

Any ideas would be greatly appreciated. Thanks in advance!

Bruce

This is the code to add a button and assign a macro:
ActiveSheet.Buttons.Add(111, 51.75, 42.75, 13.5).Select
Selection.OnAction = "Macro1"

Thanks, Ray. That worked a treat. I added the line :
Selection.Caption = "UPDATE TOC"

and that names the button correctly.

I knew I would find a solution here.

Cheers!

Bruce

Originally Posted by swatsp0p

Good morning gang. I am trying to write a macro that will create a command button on a sheet and assign VBA code to that button. Basically, my original code creates a new sheet. I would like that new sheet to have button on it that will, when clicked, run another macro.

Here's what I have (of course it doesn't work as intended. The button is created, but the label isn't changed nor is the code assigned):

Please Login or Register  to view this content.

Any ideas would be greatly appreciated. Thanks in advance!

Bruce

Hi Bruce,
Very impressive,
how can you set the range for the button, say next to the active cell??

Originally Posted by davesexcel

Hi Bruce,
Very impressive,
how can you set the range for the button, say next to the active cell??

I know it's 8 years too late but in case other users are looking for the same answer:

Please Login or Register  to view this content.