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
options.

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

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
worksheet.
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
it?

 

[Ron de Bruin]

Double click on the button and it will open the VBA editor with the click event
Add your code
Close the editor
Click the Design mode button

Test it


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Stephen sjw_ost]

Duh...I can be such a dunce sometimes.

Thank you for your help, that worked perfectly!

 

[Ron de Bruin]

You are welcome

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Duh...I can be such a dunce sometimes.

Thank you for your help, that worked perfectly!