Custom CommandBar visible dependent on active sheet

[cagey63]

Similar to an earlier post (noted below), I've created custom command
bars with associated macros pertaining to the data on a specific Excel
sheet. In other words, I want to see only the toolbar containing the
commands pertaining to the sheet when a sheet is selected. Here is
the code I've tried:

Private Sub Worksheet_Activate()
If ActiveSheet.Name = ("SheetA") Then
.CommandBars("SheetA").Visible = True
Else
.CommandBars("SheetA").Visible = False
End If

If ActiveSheet.Name = ("SheetB") Then
.CommandBars("SheetB").Visible = True
Else
.CommandBars("SheetB").Visible = False
End If

If ActiveSheet.Name = ("SheetC") Then
.CommandBars("SheetC").Visible = True
Else
.CommandBars("SheetC").Visible = False
End If
End Sub

Note that I already have code for enabling the toolbars on workbook
activation functioning.

Thanks.

Previous post:
http://groups.google.com/group/micr...read/thread/b78410b47c87d462/33ef929af02d7be1

 

[Nigel]

You will need a Worksheet_Activate event code on each sheet. Since the
code is similar I suggest you create a sub to handle all toolbars and pass
the Active sheet to this sub.

So on each sheet A,B,C etc. (code behind each sheet)

Private Sub Worksheet_Activate
myToolBars ActiveSheet
End Sub

Then in standard module

Sub myToolBars (wS as Worksheet)
CommandBars("SheetA").Visible = False
CommandBars("SheetB").Visible = False
CommandBars("SheetC").Visible = False
Select Case wS.Name
Case Is = "SheetA": CommandBars("SheetA").Visible = True
Case Is = "SheetB": CommandBars("SheetB").Visible = True
Case Is = "SheetC": CommandBars("SheetC").Visible = True
End Select
End Sub

 

[Dave Peterson]

The Worksheet_activate event lives behind each worksheet. That means that each
of worksheets that is affected would have to have very similar code--although
you don't need to check the name of the sheet--since you're already in that
sheet!

But instead of duplicating and separating the code into various worksheet
modules, you could use the Workbook_sheetactivate event that lives under
ThisWorkbook.


Private Sub Workbook_SheetActivate(ByVal Sh As Object)

application.commandbars("sheeta").visible = false
application.commandbars("sheetb").visible = false
...

select case lcase(sh.name)
case is = "sheeta" : application.CommandBars("SheetA").Visible = True
case is = "sheetb" : application.commandbars("sheetb").visible = true
....
end select
End Sub

But if you named things nicely, you could do something like:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'still hide them all -- maybe you could use a loop???
application.commandbars("sheeta").visible = false
application.commandbars("sheetb").visible = false
...

on error resume next 'in case there isn't a nicely named commandbar
application.commandbars(sh.name).visible = true
on error goto 0

End Sub

 

[cagey63]

Thanks! This works splendidly.

One additional question; if the focus shifts from any of the sheets A,
B, or C to one of the other sheets, what is the code to make the all
not visible? (The last active sheet's commandbar stays visible when
the active sheet is not A, B, or C.)

 

[cagey63]

NIgel:

To solve my dilemna (toolbar active only on selected sheet) I've tried
the following to no avail. Also, all three toolbars are visibile upon
workbook open . . .

Thanks again for your help.

Sub myToolBars(wS As Worksheet)
CommandBars("SheetA").Visible = False
CommandBars("SheetB").Visible = False
CommandBars("SheetC").Visible = False
Select Case wS.Name
Case Is = "SheetA": CommandBars("SheetA").Visible = True
Case Is = "SheetB": CommandBars("SheetB").Visible = True
Case Is = "SheetC": CommandBars("SheetC").Visible = True
Case Is <> "SheetA": CommandBars("SheetA").Visible = False
Case Is <> "SheetB": CommandBars("SheetB").Visible = False
Case Is <> "SheetC": CommandBars("SheetC").Visible = False
End Select
End Sub

 

[Nigel]

Since we hide ALL CommandBars before we turn the relevant one on, put the
Worksheet_Activate event code on every sheet not just A, B and C.
CommandBars will be disabled except for Sheets A, B or C.

Private Sub Worksheet_Activate
myToolBars ActiveSheet
End Sub

In your Workbook event, where you set up the toolbars call the myToolBars
sub......

' your code that establishes the toolbars is here

myToolBars ActiveSheet



--

Regards,
Nigel
(e-mail address removed)



NIgel:

To solve my dilemna (toolbar active only on selected sheet) I've tried
the following to no avail. Also, all three toolbars are visibile upon
workbook open . . .

Thanks again for your help.

Sub myToolBars(wS As Worksheet)
CommandBars("SheetA").Visible = False
CommandBars("SheetB").Visible = False
CommandBars("SheetC").Visible = False
Select Case wS.Name
Case Is = "SheetA": CommandBars("SheetA").Visible = True
Case Is = "SheetB": CommandBars("SheetB").Visible = True
Case Is = "SheetC": CommandBars("SheetC").Visible = True
Case Is <> "SheetA": CommandBars("SheetA").Visible = False
Case Is <> "SheetB": CommandBars("SheetB").Visible = False
Case Is <> "SheetC": CommandBars("SheetC").Visible = False
End Select
End Sub

 

[cagey63]

Since we hide ALL CommandBars before we turn the relevant one on, put the
Worksheet_Activate event code on every sheet not just A, B and C.
CommandBars will be disabled except for Sheets A, B or C.

Private Sub Worksheet_Activate
  myToolBars ActiveSheet
End Sub

In your Workbook event, where you set up the toolbars call the myToolBars
sub......

  ' your code that establishes the toolbars is here

  myToolBars ActiveSheet

--

Regards,
Nigel
(e-mail address removed)


NIgel:

To solve my dilemna (toolbar active only on selected sheet) I've tried
the following to no avail.  Also, all three toolbars are visibile upon
workbook open . . .

Thanks again for your help.

Sub myToolBars(wS As Worksheet)
 CommandBars("SheetA").Visible = False
 CommandBars("SheetB").Visible = False
 CommandBars("SheetC").Visible = False
 Select Case wS.Name
    Case Is = "SheetA": CommandBars("SheetA").Visible = True
    Case Is = "SheetB": CommandBars("SheetB").Visible = True
    Case Is = "SheetC": CommandBars("SheetC").Visible = True
    Case Is <> "SheetA": CommandBars("SheetA").Visible = False
    Case Is <> "SheetB": CommandBars("SheetB").Visible = False
    Case Is <> "SheetC": CommandBars("SheetC").Visible = False
  End Select
End Sub






- Show quoted text -

Thanks Nigel - your suggestions worked wonderfully!