Assigned Macro to button


Stephen sjw_ost

I have an excel spread sheet that I use as a template. When this template
opens, I have a userform that pops up and asks the user to select one of 2

Option 2 is to enable the user to perform maintenence or updates on the

Option 1 saves the worksheet tabs of the template to a new book and saves
the new book as a specified file name. note: I am saving the worksheet tabs
because I do not want the modules to copy to the new file, just the tabs and
any code on the worksheet tabs.

Now comes my problem. 2 of the worksheet tabs that has macro code on them
has a button on the same worksheet that the code is in and the macro code is
assigned to the button. When the save process occurs, the assignment of the
macro does not stay with the button. Instead the assignment is pointed back
to the template, instead of staying assigned to the code found on the
How can I make the macro assignment stay pointing to the code of the
worksheet the button is found on even if the tab is copied to a new workbook?

Thanks for any help.



Ron de Bruin

Hi Stephen

Use a button from the Control ToolBox(ActiveX) instead of a Forms button

This button have a click event in the sheet module and when you copy the sheet it
will copy this also to the new workbook

Private Sub CommandButton1_Click()
'enter your code here
End Sub

Stephen sjw_ost

Thank you for the prompt reply. I did your suggestion but the ActiveX Button
will not allow me to click on it to run the code. When I click on it I get
the sizing bubbles around the button. How do I get it to allow me to click on

Stephen sjw_ost

Duh...I can be such a dunce sometimes.
Thank you for your help, that worked perfectly!




Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question