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




"cagey63" wrote in message
...
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


On Jan 30, 10:46 am, cagey63 wrote:
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.)

On Jan 30, 10:13 am, "Nigel" wrote:



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

--

Regards,
Nigel


"cagey63" wrote in message

...

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/micro...wse_thr...Hide
quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -