How to stop Excel remembering/loading macro from previously opened Workbook

[Norman Yuan]

I wrote several macros with VBA code for specific tasks, which works well.
In order to for user to launch the macros easily, I created a custom toolbar
and assigned each macro to a toolbar button. Thus, user can click a button
to run a macro, instead of pressing ALT+F8 and then selecting a macro on the
list.

This approach works well except for one thing: when user opens a new Excel
work book from the customized template with the macro embedded in it (or
open a previous workbook and save it to a new file name), the toolbar button
remembers previous workbook's name and load macro from that previous file,
even though the same macros are available in this workbook. This causes the
previously worked workbook being opened undesirably. Even worse, if the
previous workbook is not available (renamed, or moved, or deleted), clicking
the toolbar button causes error message saying "xxxxxx.xls cannot be
found....". However, if user press ALT+F8 to run macro, Excel uses the macro
in the file, as expected.

Does anyone know how to stop Toolbar Button to remember where the macro is
loaded from? What is the point for Excel to remember the last file name of a
macro and loads it from there even though the same macro is in current
workbook? The ideal situation is, after assign a macro to a toolbar button.
It should only remembers macro's name. When being clicked, it should only
look into current workbook, if macro exists, run it, if macro does not
exist, report error message.

 

[kassie]

Shouldn't you store such macros in personal.xls, which is opened on startup,
so that they are available to all opened workbooks, instead of replicating
macros to all workbooks? Then you will never have this problem.

 

[Norman Yuan]

I am not sure what is "personal.xls" you referred to. I save the workbooks
with different macros as different templates( *.xlt). It is desirable way, I
thought: a different workbook template is used for different purpose, thus
no need to place all my VBA code/macros in a single file and have them all
loaded.

Of course, all the toolbars (I made toolbar group for each template) are
loaded, no matter what template is opened. In this case, if you clicks a
toolbar meant for other template and he gets error message because of
missing macro, that is fine. It not OK, howerver, that the toolbar button
always trys to load macro from previous file unless you re-assign the macros
from the currently opened workbook. It seems, to avoid this, I have to give
up using toolbar/menu and ask user to use ALT+F8 instead (and lose
user-friendliness).

 

[Dave Peterson]

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Norman Yuan]

OK, your suggestion seems my best bet. I was thinking on that direction, but
having not done coding my own toolbar/menu(CommandBar, I think it is) so
far, I thought that task would cost me more time than developing the macros.
So I asked here to see if there is a simple solution. Again, to me, it seems
not making sense that Excel's toolbar insists loading macro from previous
file, even though the macro is available in the current file. Thanks for the
suggestion and the links provided,