Programming Excel with VBA and .NET

Save and Distribute Toolbars

One key advantage of toolbars is that they can be attached to a workbook, template, or add-in without writing code. That makes them much easier to distribute than custom menus. To attach a toolbar:

1. Open the file to attach the toolbar to in Excel.

2. Choose Tools → Customize → Toolbars. Excel displays the Toolbars tab of the Customize dialog box.

3. Click Attach. Excel displays the Attach Toolbars dialog.

4. Select the custom toolbars to attach and click Copy.

5. Click OK twice to close the dialogs.

When a user opens a file that contains an attached toolbar, that toolbar is loaded and will be saved in the user’s .xlb file when she closes Excel. That makes the toolbar available to all workbooks on the user’s machine.

If you want the toolbar to appear only when the containing workbook or template is loaded, delete the toolbar in the file’s Workbook_BeforeClose event procedure. If the toolbar is attached to an add-in, use the Workbook_AddinUninstall event procedure instead. The following code handles either of those situations:

' ThisWorkbook class.
 
' For workbooks and templates.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.IsAddin Then Application.CommandBars("Menu1").Delete
End Sub
 
' For add-ins.
Private Sub Workbook_AddinUninstall( )
    If Me.IsAddin Then Application.CommandBars("Menu1").Delete
End Sub

I delete the toolbar rather than making it invisible so the user can’t try to display the toolbar after the file is uninstalled. Deleting the toolbar removes ...