best way of distributing toolbars

[sugargenius]

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?

 

[Tom Ogilvy]

Build the commandbars with code in the Workbook_Open event

http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaro97ta/html/ofcmdbar.asp
Command and Menu Bars

 

[sugargenius]

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