Moved file, changed path, macros don't work

[donbowyer]

Excel 2003 with SP2 on Win XP Home
I have an .xls file with macros run by custom toolbar buttons.
I had to move the file to another location on my hard drive and so the
reference path saved when I originally assigned the macro is no longer valid.
When I click a custom toolbar button I get a message that the macro (in the
old location) cannot be found.
If I call up the toolbar customise dialog box / assign macro, the "macro
name" window shows the file's old location. I can change this to the new one
and then save the file. The custom buttons then work OK.
But if I close the file then reopen it, the buttons no longer work, and I'm
back to square one.
I don't want to move the file back to its original location.
Any suggestions for a solution would be welcome

 

[broro183]

hi Donwb,
It sounds like the file is recreating the custom toolbar buttons each
time it is opened, for example in a "workbook open" macro.
However, the reference to the old location may also be used in other
macros within the file. To find all references to the old path & change
it to the new one in the code of all the macros:
* While in Excel press [alt + F11] to open the VB Editor (where macro
code is stored),
* press [ctrl + r] to make sure the project window is open,
* double click the "thisworkbook" option for your file,
* use [ctrl + h] (& select search current project) to find & replace
any references to the old path with the new one.
* [alt + F4] to close the VB Editor, and save your file.

Hopefully, now when you reopen your file the custom buttons will be
assigned correctly.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[donbowyer]

Many thanks broro183 - that did it.

 

[broro183]

howdy,
Thanks for the feedback, I'm pleased I could help.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...