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,

"Dave Peterson" wrote in message
...
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 wrote:

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.

--

Dave Peterson