PROBLEM : Excel automatically updates the macro locations in the toolbar
 

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

PROBLEM : Excel automatically updates the macro locations in the toolbar
 

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.

(enders2010 wrote:
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



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---