Buttons and macros

[DevalilaJohn]

I have a spreadsheet template MyTemplate.xlt. It has a couple of custom
buttons in it that are tied to macros. I have the macros assigned to the
buttons as MyTemplate.xlt!MyMacro looking only at macros in MyTemplate.xlt.

When I open a spreadsheet using the template (File| New...) the spreadsheet
opens and sometimes the buttons work correctly. If I close Excel (2003) and
reopen the buttons generate an error message that the macro
....MyTemplate1.xlt!MyMacro cannot be found. When I go back to the template,
the macro assignment has been changed and matches what the message indicated.

I've tried this on a couple of computers and gotten the same result. How
does the macro assignment get changed in the template to match a spreadsheet
copy generated from it? I'd appreciate any suggestions.

TIA,

John

 

[Dave Peterson]

You could reassign each macro to the correct button each time the workbook
opened...

But you may find it easier to use the commandbuttons from the Control Toolbox
toolbar instead of the buttons from the Forms toolbar.

Instead of assigning macros, you can just click on the commandbutton and see
where the code goes. (It's under the worksheet module--not in a general
module. So you may have to make changes to your code, too.)

 

[DevalilaJohn]

Dave,

Thanks for the suggestion, that is a good workaround. I'm still curious as
to why the toolbar buttons change the source of their macro.

 

[Dave Peterson]

The bad news is that it doesn't change the location of the macro. It still
wants to go off and find the macro in your .xlt workbook. And that's usually a
bad idea--lots of times, the template isn't shared with the recipients, so it
can't be found.