changing drive letter in custom macro toolbar buttons

[Govt Guy]

I periodically run into a problem with custom buttons on my Excel
toolbar linked to macros in my personal.xls file where the actual
location (drive and/or folder) of personal.xls no longer matches what is
associated with the custom button on the toolbar. For example, I get a
new computer and decide to install Windows and Excel on drive "D"
instead of drive "C". [Don't ask why... it's a long story!] I install
Excel and copy my old personal.xls into the appropriate folder onthe new
drive "D" but the custom button on the toolbar still thinks personal.xls
is on drive "C". This results in the old "two copies of personal.xls
have been opened" error message and the macros don't work.

I can fix the problem by going into tools:customize:macros, highlighting
the button, and usin modify selection to manually reassign each button
to the correct location for personal.xls, but by now I have about 20
custom buttons and it's a pain. Is there some secret file or registry
entry that stores information about custom buttons where I could go in
and change the drive/folder locations a little more easily? I think yes
since I apparently accomplished it last week... I just don't know what I
did!

Any help deeply appreciated.




*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

One way to make your life much simpler is to build that toolbar on the fly:

Here's how I create it:
http://groups.google.co.uk/[email protected]

If you want to add items to the worksheet menu bar, you can use John
Walkenbach's menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm

I periodically run into a problem with custom buttons on my Excel
toolbar linked to macros in my personal.xls file where the actual
location (drive and/or folder) of personal.xls no longer matches what is
associated with the custom button on the toolbar. For example, I get a
new computer and decide to install Windows and Excel on drive "D"
instead of drive "C". [Don't ask why... it's a long story!] I install
Excel and copy my old personal.xls into the appropriate folder onthe new
drive "D" but the custom button on the toolbar still thinks personal.xls
is on drive "C". This results in the old "two copies of personal.xls
have been opened" error message and the macros don't work.

I can fix the problem by going into tools:customize:macros, highlighting
the button, and usin modify selection to manually reassign each button
to the correct location for personal.xls, but by now I have about 20
custom buttons and it's a pain. Is there some secret file or registry
entry that stores information about custom buttons where I could go in
and change the drive/folder locations a little more easily? I think yes
since I apparently accomplished it last week... I just don't know what I
did!

Any help deeply appreciated.

*** Sent via Developersdex http://www.developersdex.com ***

 

[Govt Guy]

Thanks Dave. You suggested a couple of options that seem to provide me a
little more control over the toolbar. The menu is cool, but it invloves
an extra step of displaying the drop down menu before clicking the
desired macro.

I'm still wondering if there is a secret VBA or Excel system file
somewhere that holds the info on custom buttons linked to macros added
to the Excel toolbar through the Tools:Customize:Macros route.

I know the info (e.g., on the drive/directory holding the macro file)
must be stored somewhere because when I use Tools:Customize:Macros,
highlight a custom button, and Modify Selection:Assign Macro, "Macro
Name" displays both the drive letter and the folder location of (in my
case) Personal.xls.

Since I have dozens of macros defined in Personal.xls and associated
with custom buttons, I'd like an easier way of modifying that "location"
than highlighting each custom button and using Modify Selection:Assign
Macro.

Maybe the best solution is to create a custom toolbar and add it in as
an add-in?

*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

I don't think it's secret or hidden. You found out how to change it manually in
your original post. (It's enough of a pain to do once, that you don't want to
do a second time, though!)

You could go through all the toolbars and change the .onaction from the old
location to the new location:

http://groups.google.co.uk/group/mi...subject:Laptop"&rnum=1&hl=en#c6b36967034ce7cb

or

http://snipurl.com/hwet

But if you're gonna do this kind of thing, I think I'd just invest the time in
creating the toolbar on the fly.

(Once you have it done, you're done!)

ps. did you look at that other link?