Visibilise toolbars/menus based on active sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 procedure:

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
 
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 procedure:

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







.
Click to expand...
 
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
 
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
 
Back
Top