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
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.