| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom commandbar (1 works, 1 doesn't) | Excel Programming | |||
| CommandBar not Visible on Chart Pages | Excel Programming | |||
| Commandbar not visible in a graphic | Excel Programming | |||
| Custom Commandbar | Excel Programming | |||
| Value of ComboBox on Custom CommandBar | Excel Programming | |||
Linear Mode
