PROBLEM : Excel automatically updates the macro locations in the toolbar

[enders2010]

Hi,

I encountered the following problem.
- Create a blank wordbook
- Add the following procedure in VBA
Public Sub test()
MsgBox ThisWorkbook.FullName
End Sub

- now create a custom toolbar and name it abc
- assign a button to it and assign the test procedure to the button Test
- save the workbook as test1
- press the Test button.
It should say C:\Test1.xls
- Quit excel
- Start excel but don't open the book
- Close any open workbook
- Press the Test button.
Excel will open the workbook and say C:\Test1.xls
- Now do File | save as and save the file as C:\test2.xls
- Press the Test button
It will now say C:\test2.xls while I was expecting C:\Test1.xls

Is there anyway to prevent this from happening ?
Is there a way to correct it ?
(I was thinking of programmatically reassign the marco's)
Is it possible to detach the toolbar from test2 by using VBA ?
If you do Tool | Customize | Attach you can copy/remove a toolbar into a workbook.
Unfortenately when you start recording, you get an empty macro.
Any idea how to do that fro VBA ?

With regards,

Constantijn Enders

 

[Dave D-C]

It seems to me that what you want is a macro in Personal.xls:
Public Sub test()
Workbooks.Open FileName:="D:\Test1.xls"
End Sub
Attach that to your toolbar button.
Now it always loads Test1, no matter how you save it.