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...ef929af02d7be1

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
(E-Mail Removed)



"cagey63" <(E-Mail Removed)> wrote in message
news:06d39d28-e2a5-4cf9-a548-(E-Mail Removed)...
> 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...ef929af02d7be1

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 wrote:
>
> 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...ef929af02d7be1

--

Dave Peterson

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" <nigel-...@nosupanetspam.com> 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
> nigelnos...@9sw.co.uk
>
> "cagey63" <the_cagey_...@excite.com> wrote in message
>
> news:06d39d28-e2a5-4cf9-a548-(E-Mail Removed)...
>
>
>
> > 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...se_thr...-Hide quoted text -
>
> - Show quoted text -

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 <the_cagey_...@excite.com> 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" <nigel-...@nosupanetspam.com> 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
> > nigelnos...@9sw.co.uk
>
> > "cagey63" <the_cagey_...@excite.com> wrote in message
>
> >news:06d39d28-e2a5-4cf9-a548-(E-Mail Removed)...
>
> > > 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...se_thr....Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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 Removed)



"cagey63" <(E-Mail Removed)> wrote in message
news:74bf552f-90f1-4e5d-bba3-(E-Mail 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


On Jan 30, 10:46 am, cagey63 <the_cagey_...@excite.com> 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" <nigel-...@nosupanetspam.com> 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
> > nigelnos...@9sw.co.uk
>
> > "cagey63" <the_cagey_...@excite.com> wrote in message
>
> >news:06d39d28-e2a5-4cf9-a548-(E-Mail Removed)...
>
> > > 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 -

On Jan 31, 1:11*am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
> 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
> nigelnos...@9sw.co.uk
>
> "cagey63" <the_cagey_...@excite.com> wrote in message
>
> news:74bf552f-90f1-4e5d-bba3-(E-Mail 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
>
> On Jan 30, 10:46 am, cagey63 <the_cagey_...@excite.com> 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" <nigel-...@nosupanetspam.com> 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
> > > nigelnos...@9sw.co.uk
>
> > > "cagey63" <the_cagey_...@excite.com> wrote in message
>
> > >news:06d39d28-e2a5-4cf9-a548-(E-Mail Removed)....
>
> > > > 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...isc/browse_thr....
> > > >quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Thanks Nigel - your suggestions worked wonderfully!