Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visibilise toolbars/menus based on active sheet
Any ideas on how to show and hide toolbars automatically
based on the currently active sheet in a workbook. So have some toolbars/menus only applicable to some sheets? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visibilise toolbars/menus based on active sheet
every sheet has event called "Activate" (in VBA project, select the sheet
you want, and double-click on it to see it's code. Then choose event Activate). In this event, you can show or hide or modify toolbars as you want. For example, here is a fragment of the code I used to make a custom toolbar context-sensitive: My model has 4 sheets, "Table", "Chart", "Main" and "Notes". I want to see different toolbars, depending on the active sheet. My approach: I created one toolbar ("MyToolbar"), and all kinds of buttons for it. Then, depending on the active sheet, I make some buttons visible, and hide others. The illusion is that the toolbar is context-sensitive. Every sheet contains this procedu Private Sub Worksheet_Activate() Update_Toolbar End Sub It calls a sub from a regular module: Sub Update_Toolbar() ' turn-off screen so that user does not see your manipulations Application.ScreenUpdate=False ' hide current toolbar buttons ' simple procedure - just cycle through all buttons and set their property Visible to False Hide_MyToolbar_Buttons ' Choose_toolbar configuration based on active sheet ' Create_MyToolbar is a simple sub which makes a desired set of controls visible ' by setting their Visible property True Select Case ActiveSheet.Name Case shChart: Create_Chart_Toolbar ' where shChart is a constant with the sheet name Case shTable: Create_Table_Toolbar Case shMain: Create_Main_Toolbar Case Else: Create_Notes_Toolbar End Select ' Show new toolbar Application.ScreenUpdate=True End Sub Cheers, RADO wrote in message ... Any ideas on how to show and hide toolbars automatically based on the currently active sheet in a workbook. So have some toolbars/menus only applicable to some sheets? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visibilise toolbars/menus based on active sheet
Rado, Thanks very much.
-----Original Message----- every sheet has event called "Activate" (in VBA project, select the sheet you want, and double-click on it to see it's code. Then choose event Activate). In this event, you can show or hide or modify toolbars as you want. For example, here is a fragment of the code I used to make a custom toolbar context-sensitive: My model has 4 sheets, "Table", "Chart", "Main" and "Notes". I want to see different toolbars, depending on the active sheet. My approach: I created one toolbar ("MyToolbar"), and all kinds of buttons for it. Then, depending on the active sheet, I make some buttons visible, and hide others. The illusion is that the toolbar is context-sensitive. Every sheet contains this procedu Private Sub Worksheet_Activate() Update_Toolbar End Sub It calls a sub from a regular module: Sub Update_Toolbar() ' turn-off screen so that user does not see your manipulations Application.ScreenUpdate=False ' hide current toolbar buttons ' simple procedure - just cycle through all buttons and set their property Visible to False Hide_MyToolbar_Buttons ' Choose_toolbar configuration based on active sheet ' Create_MyToolbar is a simple sub which makes a desired set of controls visible ' by setting their Visible property True Select Case ActiveSheet.Name Case shChart: Create_Chart_Toolbar ' where shChart is a constant with the sheet name Case shTable: Create_Table_Toolbar Case shMain: Create_Main_Toolbar Case Else: Create_Notes_Toolbar End Select ' Show new toolbar Application.ScreenUpdate=True End Sub Cheers, RADO wrote in message ... Any ideas on how to show and hide toolbars automatically based on the currently active sheet in a workbook. So have some toolbars/menus only applicable to some sheets? Thanks . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visibilise toolbars/menus based on active sheet
Hi Rado,
I am working through your notes. How do I choose the event Activate (line 2-3 of your notes??) I don't seem to be able to find it Cheers -----Original Message----- every sheet has event called "Activate" (in VBA project, select the sheet you want, and double-click on it to see it's code. Then choose event Activate). In this event, you can show or hide or modify toolbars as you want. For example, here is a fragment of the code I used to make a custom toolbar context-sensitive: My model has 4 sheets, "Table", "Chart", "Main" and "Notes". I want to see different toolbars, depending on the active sheet. My approach: I created one toolbar ("MyToolbar"), and all kinds of buttons for it. Then, depending on the active sheet, I make some buttons visible, and hide others. The illusion is that the toolbar is context-sensitive. Every sheet contains this procedu Private Sub Worksheet_Activate() Update_Toolbar End Sub It calls a sub from a regular module: Sub Update_Toolbar() ' turn-off screen so that user does not see your manipulations Application.ScreenUpdate=False ' hide current toolbar buttons ' simple procedure - just cycle through all buttons and set their property Visible to False Hide_MyToolbar_Buttons ' Choose_toolbar configuration based on active sheet ' Create_MyToolbar is a simple sub which makes a desired set of controls visible ' by setting their Visible property True Select Case ActiveSheet.Name Case shChart: Create_Chart_Toolbar ' where shChart is a constant with the sheet name Case shTable: Create_Table_Toolbar Case shMain: Create_Main_Toolbar Case Else: Create_Notes_Toolbar End Select ' Show new toolbar Application.ScreenUpdate=True End Sub Cheers, RADO wrote in message ... Any ideas on how to show and hide toolbars automatically based on the currently active sheet in a workbook. So have some toolbars/menus only applicable to some sheets? Thanks . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visibilise toolbars/menus based on active sheet
I am also having problems working out how to refer to the
menu items. Can I refer to menu items as "Controls" and set there property visible? If so how? (or does it have to be only control buttons?) Thanks Cheers -----Original Message----- Rado, Thanks very much. -----Original Message----- every sheet has event called "Activate" (in VBA project, select the sheet you want, and double-click on it to see it's code. Then choose event Activate). In this event, you can show or hide or modify toolbars as you want. For example, here is a fragment of the code I used to make a custom toolbar context-sensitive: My model has 4 sheets, "Table", "Chart", "Main" and "Notes". I want to see different toolbars, depending on the active sheet. My approach: I created one toolbar ("MyToolbar"), and all kinds of buttons for it. Then, depending on the active sheet, I make some buttons visible, and hide others. The illusion is that the toolbar is context-sensitive. Every sheet contains this procedu Private Sub Worksheet_Activate() Update_Toolbar End Sub It calls a sub from a regular module: Sub Update_Toolbar() ' turn-off screen so that user does not see your manipulations Application.ScreenUpdate=False ' hide current toolbar buttons ' simple procedure - just cycle through all buttons and set their property Visible to False Hide_MyToolbar_Buttons ' Choose_toolbar configuration based on active sheet ' Create_MyToolbar is a simple sub which makes a desired set of controls visible ' by setting their Visible property True Select Case ActiveSheet.Name Case shChart: Create_Chart_Toolbar ' where shChart is a constant with the sheet name Case shTable: Create_Table_Toolbar Case shMain: Create_Main_Toolbar Case Else: Create_Notes_Toolbar End Select ' Show new toolbar Application.ScreenUpdate=True End Sub Cheers, RADO wrote in message ... Any ideas on how to show and hide toolbars automatically based on the currently active sheet in a workbook. So have some toolbars/menus only applicable to some sheets? Thanks . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Menus and Toolbars | Excel Discussion (Misc queries) | |||
Cannot unhide toolbars/menus | Excel Discussion (Misc queries) | |||
Custom Menus and Toolbars | Excel Discussion (Misc queries) | |||
Missing menus and toolbars | Excel Discussion (Misc queries) | |||
Restricting use of menus and toolbars | Excel Programming |