Problem:Template with ToolBar and Macros 1

[MrPtt]

Hi
That's the problem I found:

I have a .xlt, with a ToolBar. I create the ToolBar by code, and assign to the "onAction" even of every button the name of the macro it calls.
By now, all OK

If I open a Workbook, it works fine. But if I open a secon Workbook (based on the same .xlt) I found the problem: The second Workbook doesn't create the Toolbar, because It exists, but if I push a button, it calls the macro of the first Workbook opened, not of the actual Workbook.

And, if I close the first Workbook, when I push a button, then Excel try to open the document that created the ToolBar(The Toolbar refence it)

I don't know if I explained clearly...

The fast solution I'm searching it's:
o_Command.onAction = "ActiveWorkBook.MacroX"

But I don't know how to access Macros throught the ActiveWorkBook Object. It's possible?

I sorry that looooong explanation.
Thanks in advance

 

[nickkeyes]

You can get around this problem by saving your macro workbook seperately as an excel add in. You then need to assign each toolbar item to the desired macro.

NOTE:
The macros stored in your excel add in are not displayed when you try to view macros in 'all open workbooks'. You can select it by typing its unique name.
You'll need to carefully name each macro and module to ensure it is not confused with macros in other workbooks. e.g don't use Macro1 etc.

This method always calls the same macro. Causes problems if you want to distribute the file cos the add ins are stored in different locations on different OS and requires the users to install the add in.

 

[MrPtt]

Thanks nickkeyes

In other thread I found a second possible solution:
I noticed that when I try to save a new macro, I can select the option "save in: Personal workbook". This generates a new workbook (Personal.xls), that you only can see when go to code.
I hope macros stored in that workbook are accesible anywhere. I'm testing that also.

Thanks
See you

 

[michaelbr55]

Hi MrPtt,
I think you have the best solution to your problem in Personal.xls, now you just need to save it in "C:\Program Files\Microsoft Office\Office\XLStart" or whatever, so it starts everytime Excel starts and make it hidden ie Windows, Hide so the worksheet for it doesn't show.

 

[MrPtt]

Hi all
I have the same problem with the two possible solutions:
1.- Add in
2.- Personal.xls

Both are stored in different locations on different OS (as said nickkeyes), and the .xlt I'm developing It's suposed to be distributed "as is". Without installation and dependences.

I'll continue retrieving information...
Thaks