Hack 11 Tie Custom Toolbars to a Particular Workbook

Although most toolbars you build apply to just about any work you do, sometimes the functionality of a custom toolbar applies to only one workbook in particular. With this hack, you can tie custom toolbars to their respective workbooks .

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. Whenever you open or activate another workbook, your custom toolbar disappears and isn't accessible. Reactivate the appropriate workbook, and poof! 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's gone; switch back and it reappears like magic.