Buttons that trigger macros still refer to the 'template' workbook's macros when doing Save As and saving a new workbook

relaxok

New Member
Joined
Apr 14, 2009
Messages
27
I have a 'template' .xlsm workbook that several processes use - it's opened up, customized, then SaveAs'd in VBA to a new document.

There are 2 buttons in the template workbook that trigger public macros in the vba project.

When the buttons are clicked in the newly saved workbook, it opens the template workbook and runs the macro there.

The code is the same and only changes Application values so it doesn't cause any problems, but it means any time someone uses the buttons in the new workbook it opens the template workbook and gets focus, which is quite annoying.

How can I make the buttons in the SaveAs'd new workbook point to its own version of the macros without having to reassign what the buttons do each time?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
199
Office Version
  1. 365
Platform
  1. Windows
Try this
Open the template workbook, and right click on each button, then from the pop up menu, pick Assign macro
This will show a list of macros and a textbox at top with the macro to run for this button, including the template filename
Delete the filename, just leaving the macro name (eg if the textbox reads MyTemplate.xlsb!SomeMacro, change to SomeMacro)
Click OK
Do both buttons, then save and test if this works
 

relaxok

New Member
Joined
Apr 14, 2009
Messages
27
Try this
Open the template workbook, and right click on each button, then from the pop up menu, pick Assign macro
This will show a list of macros and a textbox at top with the macro to run for this button, including the template filename
Delete the filename, just leaving the macro name (eg if the textbox reads MyTemplate.xlsb!SomeMacro, change to SomeMacro)
Click OK
Do both buttons, then save and test if this works

Thanks for getting me started looking at that - I noticed that when doing this if you look at the assignment again, it's pointing specifically to the current workbook still. However after looking at this whole issue more, I actually realized I misdiagnosed the problem. SaveAs DID work already - it was copying a sheet from the template to the new workbook which retained the old macro assignment, even though the new workbook has macros of the same name.

I'm now just specifying e.g. NewBook.NewSheet.Shapes("Button 1").OnAction = 'NewBook'!MacroName via VBA after it copies the sheet since I didn't see any other way to 'shift' the macro to point to the book you're copying it into.

All good now!
 

Forum statistics

Threads
1,176,499
Messages
5,903,413
Members
435,028
Latest member
excelvbanoob420

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top