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

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

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


.

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


.

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


.

.