Macro call from custom button opens old file

[Guest]

I have created several macros in a spreadsheet that work fine on my computer.
They also work fine on several other collegues computers, but one collegue
has trouble when he saves the file after I e-mail it to him. The VBA code
that I wrote no longer works on his computer because the button still seems
to contain a reference to the file name that I created the macro under. So,
when he opens the file, it opens the old excel file that I sent to him first
and the macro does not function properly. Here is the sequence of events.
He opens the file (his macro security is set to Medium, so he has to choose
the "enable Macros" option. When he presses the button in question, Excel
asks him to "enable Macros" again and attempts to open another file (of the
same name as what I sent to him). I am not sure that I am adequately
describing the problem. I would be glad to provide more info if someone will
try to help. Thank you in advance.

 

[Bob Flanagan]

It sounds like he is renaming the file from outside Excel (IE, via his email
save). Have him save it by the original name, open it in Excel and do a
save as if he wants to rename it. This will change the references on the
buttons.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

Ok, you were close. Your answer definitely got me thinking and then I found
out what is happening. What happened is that the first time my collegue
opened this file the toolbar that I created was installed. When he opened it
the first time he launched it from Lotus Notes, so it attached some made up
temp name on the file. So, the buttons were referring to this temp name. So
when he saved it out of an e-mail again, the buttons in the toolbar were
still pointing at this temp name, so the Excel did not change the reference
when he did a save as because the temp name in the toolbar did not match the
name of the file.

So, now I have another issue. The toolbar is always present in Excel, if I
open a blank workbook and try to use the toolbar, it just opens the
spreadsheet that the buttons refer to. Instead, I would rather it say
something like, Macro Not Found. Is there a way to make the reference on the
button local only to the sheet that is opened? If not, I may have to make
these buttons on the spreadsheet instead of in the toolbar.