Customer Toolbar Issues

[ihsir]

I've created a custom toolbar and assigned macros to some buttons on
that toolbar.

1. On the Tools menu, click Customize, and then
click the Commands tab.
2. Under Categories, click Macros.
3. Drag the custom button to the toolbar where you
want it.
4. On the Customize dialog box, click Modify
Selection, and then click Assign Macro.
5. In the Assign Macro dialog box, click the name of
your macro, and then click OK.

1. Every week I send an updated file to users, but the macros on the
toolbar seem to be pointing to the previous file (from previous week)
or to the old location. How do I have the macros on the toolbar point
to the new file?
2. Once someone opens the excel file (report) that has this toolbar
attached to it, the toolbar always shows up in excel. Is there any
automated way to close/remove the toolbar once the particular report
(file) is closed? This toolbar remains in excel even though the file
has been closed. Each user may manually delete the toolbar but
because many users have received and opened the file, is there any
automated way to remove the toolbar?

Any help will be greatly appreciated.

 

[Chip Pearson]

You should never manually create a command bar and attach it to a
workbook for distribution. You'll run into all sorts of problems, not
the least of which are command bars that are not up to date and, as
you've seen, command controls pointing to the wrong workbook.

Instead, always use VBA code to create the command bars and command
controls, every time the workbook is opened. Set everything to
Temporary:=True so nothing gets stored in a XLB file.

The best approach for this sort of thing is to take the code that
handles the command controls out of the workbook itself and put it in
an XLA add-in. This separates code from data so you can redistribute
the new XLA with the revised controls and users can load that add-in
but continue to use the existing XLS workbooks.

I have some rather old but still relevant information and example code
for using VBA to create menu items and command bar controls at
http://www.cpearson.com/excel/menus.htm#vba

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)