Toolbar macros, Personal.xls and the .xlb file

[David Brand]

Several of my toolbar buttons are associated with macros. Whenever I
update the toolbars I copy my .xlb file to the other 2 or 3 machines I
work on. However my Personal.xls file is not located in the same
folder on each machine. Consequently after copying the new .xlb file
across to the other machines, I have to re-point each button to the
correct macro. Is there an easier way of doing this – eg.
Programmatically. Alternatively, it seems the information about the
location of the Personal.xls file is located in the .xlb file. Is
there some way to modify the .xlb file?

Another problem that seems to be related to replacing the .xlb file is
that I lose the Security menu from the Tools | Macro sub-menu. How can
I restore this eg. Is this a registry setting or is it also set in the
..xlb file?

Thanks, David

 

[Bernie Deitrick]

David,

A better way to do this would be to have your Personal.xls contain
code to create-on-open and destroy-on-close the commandbar buttons and
do all the macro assignments. There are a lot of code examples in the
newsgroups, so do a Google group search. Then all you would need to do
is copy your Personal.xls to the various machines, and your settings
and .xlb files would be fine.

HTH,
Bernie
MS Excel MVP

 

[David McRitchie]

Hi David,

For what you are currently doing. You want to restore your
*.xlb file and your personal.xls files to the same path as they
are on the new machine with Excel down.

If that doesn't work and certainly for your current situation you
will have to read through your toolbar menus and buttons,
changing the links. Bill Manville may have something more
up todate in the excel.links newsgroup, but what I've used
recursive, updating custom toolbars «, Bill Manville,
1998/01/17, Recursively Fixing your Toolbars and Menus,
after moving or restoring your *.xlb
http://google.com/[email protected]

Menus and buttons are pretty much the same, so you will also have
a problem with menus. Things concerning menus follow:

An example of handling menus is John Walkenbach's
Menu Maker, tip 53 don't know if he has one for buttons
http://j-walk.com/ss/excel/tips/tip53.htm

If you want to convert your menus to a spreadsheet that you
can rework for Menu Maker see
http://www.mvps.org/dmcritchie/excel/barhopper.htm
and you have to do the buttons on the menu manually.
Ignores the buttons on the toolbars, which is what you were
really after.

Backup considerations in
http://www.mvps.org/dmcritchie/excel/backup.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm