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