Templates/Menus

[Michelle]

Hi all

I have a template in which I have created a new menu on
the standard menubar.

I have assigned the relevant macros and whilst in design
mode in the template macros work fine. However, when I
use File New and click the menu buttons, excel is looking
at the macros in the original template and therefore opens
that to run the macros. How do I define when creating a
custom menu that the macros in ThisWorkbook should be run.

Many thanks
Michelle

 

[Freemini]

Did you save the macro in Personal.xls or just another workbook?

If the macro is held in your personal workbook it should run withou
any problems.

Mik

 

[Dave Peterson]

How did you build the menu?

If you built it using code (like in the auto_open procedure or workbook_open
event), then you probably used something like:


With somevariablehere
.OnAction = "mymacroname"
'....
End with

You could change that .onaction line to point at the current workbook.

.onaction = thisworkbook.name & "!mymacroname"

=====
Another alternative maybe to extract the menu macro from your template. Put the
code into an addin. Then you'd only have the code in one spot (maybe easier for
updating). And the macros in the template file wouldn't cause problems--cause
they'd be gone!)

Tell the user to open the addin when they need the macro.

You could even put a reference to the addin in your template file. Then when
you used the template to create a new workbook, excel would open that addin.

If you like that, then in your addin in the VBE:
hit ctrl-R to view the project window
find your addin's project.
Hit F4 to view the Properties window
Then rename the addin's project (from VBAProject) to something nice and unique.
Save your addin

Then in the template file (also in the VBE),
Tools|Reference
and find that nicely named project and put a check next to it.

Then save and close everything.

Then open excel|File|New
and see if the addin got loaded and the menubar got created.