Customised Toolbar Specific to only 1 Worksheet

[Guest]

MS Excel 2003

I have created a custom toolbar (called Toolbar) and assigned some macros to
it. I created the toolbar using the standard right
click>customise>Toolbars>new.

I want the floating toolbar to only be displayed when a particular sheet is
being viewed ("Schedule") and hidden if it is not. As such under the
Schedule Sheet I have added the following code

Private Sub Worksheet_Activate()
If ActiveSheet.Name = ("Schedule") Then
CommandBars("Toolbar").Enabled = True
CommandBars("Toolbar").Visible = True
Else:
CommandBars("Toolbar").Visible = False
End If
End Sub

Enabling and showing the toolbar works when I select the schedules sheet.
However, as the schedules sheet is the default sheet displayed I need to
first select another sheet and then reselect schedules before the toolbar is
displayed.

Once the toolbar is displayed and I select another sheet (any other sheet
besides schedules) I want it to be hidden from view.... but it isnt.

I have the same problem with the toolbar being visible when I close the
workbook. I need the toolbar to be closed when the workbook is closed.

Hope one of you guys can point me in the right direction.

Thanks

Carmi

 

[Guest]

Best to attach the toolbar to your workbook. Make sure it's open and then,
in View, Toolbars, Customize click Attach then highlight your toolbar and
copy it across. Save and close the workbook and you can now delete the
toolbar to remove it from your standard Excel setup so it doesn't appear for
every other file you open.

Next, use both the Activate and Deactivate events for your worksheet:

Private Sub Worksheet_Activate()
CommandBars("test").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
CommandBars("test").Visible = False
End Sub

 

[Guest]

Martin - thanks for your reply. Your answer was spot on... cant beleive it
was so easy!

Appreciate your help

Carmi