MS-Excel / General Formatting

---

Custom Toolbars to a Particular Workbook

If you've ever created a custom toolbar, you have no doubt noticed that the toolbar is loaded and visible regardless of which workbook you have open. What if your custom toolbar contains recorded macros meant only for a specific workbook? It's probably best to tie special-purpose custom toolbars to the appropriate workbooks to reduce both clutter and possible confusion. You can do this by inserting some very simple code into the private module of the workbook.

To get to this private module, right-click the Excel icon, which you'll find at the top left of your screen, next to File, and select View Code.

This shortcut isn't available on the Mac. You'll have to open the Visual Basic Editor (VBE) by pressing Option-F11 or by selecting Tools → Macro → Visual Basic Editor. Once you're there, Ctrl-click or right-click This Workbook in the Projects window.

Then, enter this code:

    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, then save your workbook. Whenever you open or activate another workbook, your custom toolbar disappears and isn't accessible. Reactivate the appropriate workbook. The toolbar's back.

You even can take this down a level, making the custom toolbar available only to a specific worksheet within the workbook. Right-click the Sheet Name tab of the sheet on which you want the toolbar to be accessible and select View Code. 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 is gone; switch back and it reappears like magic.