Custom CommandBar visible dependent on active sheet
 

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

Custom CommandBar visible dependent on active sheet
 

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

Custom CommandBar visible dependent on active sheet
 

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

Custom CommandBar visible dependent on active sheet
 

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...browse_thr...- Hide quoted text -

- Show quoted text -

Custom CommandBar visible dependent on active sheet
 

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...se_thr....Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Custom CommandBar visible dependent on active sheet
 

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 -

Custom CommandBar visible dependent on active sheet
 

On Jan 31, 1:11*am, "Nigel" 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


"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...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!