Categories
Excel

Tie Custom Toolbars to a Specific Workbook or Worksheet

Some workbooks require specific custom toolbars to enhance their functionality. You can learn how to link these toolbars to their corresponding workbooks with this tutorial. This way, you can avoid cluttering your interface with unnecessary toolbars that only apply to certain workbooks.

Note: If you are using an earlier version of Office (i.e. Office 97-2003), this tutorial is for you. Since Microsoft Office 2007, the use of CommandBars has been superseded by the new ribbon component.

You may want to limit the visibility of your custom toolbar to a specific workbook, especially if it contains macros that are only relevant to that workbook. To achieve this, you need to add some simple code to the private module of the workbook that controls when the toolbar is loaded and unloaded:

  1. Press Alt+F11 (Option+F11) to open the VBE (Visual Basic Editor).
  2. Enter this code in the ThisWorkbook code module:
Private Sub Workbook_Activate( )
 On Error Resume Next
   With Application.CommandBars("MyCustomToolbar")
     .Enabled = True
     .Visible = True
   End With
 On Error GoTo 0
End Sub

Private Sub Workbook_Deactivate( )
 On Error Resume Next
   Application.CommandBars("MyCustomToolbar").Enabled = False
 On Error GoTo 0
End Sub

Change the text “MyCustomToolbar” to the name of your own custom toolbar. To get back to the Excel interface, close the module window or press Alt+Q (or ⌘+Q).

Whenever you open or activate another workbook, your custom toolbar disappears and isn’t accessible. Reactivate the workbook containing the above, and the toolbar’s back.

Making the custom toolbar available only to a specific worksheet

You even can take this down a level, making the custom toolbar available only to a specific worksheet within the workbook.

  1. Right-click the Sheet Name tab of the sheet on which you want the toolbar to be accessible
  2. Select View Code.
  3. Enter this code:
Private Sub Worksheet_Deactivate( )
 On Error Resume Next
   Application.CommandBars("MyCustomToolbar").Enabled = False
 On Error GoTo 0
End Sub
Private Sub Worksheet_Activate( )
 On Error Resume Next
   With Application.CommandBars("MyCustomToolbar")
    .Enabled = True
    .Visible = True
   End With
 On Error GoTo 0
End Sub

Now press Alt+Q or close the window to get back to Excel.

The first procedure (Worksheet_Deactivate( )) will fire automatically each time you leave that particular worksheet to activate another one.

The firing of the code changes the Enable property of your custom toolbar to False so that it cannot be seen or displayed.

The second procedure is fired each time you activate the worksheet and sets the Enable property of your custom toolbar to True so that it can be made visible.

The line of code that reads Application.CommandBars(“MyCustomToolbar“).Visible = True simply displays your custom toolbar again, so the user can see it. Switch worksheets and the toolbar’s gone; switch back and it reappears like magic.