Excel Hacks Ebook [Electronic resources] نسخه متنی

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.