Constraining custom toolbars to one file

  • Thread starter Thread starter buczacz
  • Start date Start date
B

buczacz

In Excel95, I have a workbook with a custom heading category (with
sub-headings and sub-sub-headings) added to the menu bar. When selected,
the sub-headings trigger the running of associated macros. If while
that workbook is open, one opens yet another (perhaps unrelated)
workbook, the custom headings also appear as options on the menu bar of
that workbook. Their presence on the menu bar above the unrelated
workbook creates confusion and the potential for inducing errors.

I note the situation is unchanged when using Excel97.

Is there a way to restrict the display of those menu bar additions to
when the applicable workbook is displayed? Might there be another way
to gain the same functionality that would eliminate the confusion?

Buczacz
 
Buczacz,

Copy the code below into the codemodule of the ThisWorkbook object in
your project. Change the commandbar name and control name as
appropriate.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("BarName").Controls("ControlName").Visible
= True
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("BarName").Controls("ControlName").Visible
= False
End Sub
 
Back
Top