I have a workbook with macros that I need to distribute to users. I'd
like to expose macros through toolbar. At first I thought, I'd just
attach toolbar to workbook, but then I thought of this situation:

book named "test1.xls"
give to user
user saves to d:\temp\test1.xls
user opens and gets toolbar
user decides to move test1.xls to C:\Documents and Settings\joeblow\My
Documents\test1.xls
now buttons on toolbar refer to file that no longer exists

What's the best way to avoid this?

Build the commandbars with code in the Workbook_Open event

http://msdn.microsoft.com/library/de...l/ofcmdbar.asp
Command and Menu Bars

--
Regards,
Tom Ogilvy



"sugargenius" wrote in message
oups.com...
I have a workbook with macros that I need to distribute to users. I'd
like to expose macros through toolbar. At first I thought, I'd just
attach toolbar to workbook, but then I thought of this situation:

book named "test1.xls"
give to user
user saves to d:\temp\test1.xls
user opens and gets toolbar
user decides to move test1.xls to C:\Documents and Settings\joeblow\My
Documents\test1.xls
now buttons on toolbar refer to file that no longer exists

What's the best way to avoid this?

Thanks Tom, that's just what I needed.

BTW, I used a constant to name my toolbar, and it seems to be
"unavailable" to the Workbook_Close event:

Private Const COMMANDBAR_NAME = "Prep Labor Actuals"

Sub Workbook_Close()
Call RemoveToolBar
End Sub

' this works
Sub RemoveToolBar()
On Error Resume Next
Application.CommandBars("Prep Labor Actuals").Delete
End Sub

' this doesn't work
Sub RemoveToolBar()
On Error Resume Next
Application.CommandBars(COMMANDBAR_NAME).Delete
End Sub