Worksheet toolbar

[Slim Slender]

The following code works perfectly on my machine. The custom toolbar
is only visible when one specific sheet is active and disappears when
I switch to another sheet or another workbook. But when the WB is
opened on someone else's machine the toolbar appears and will stay
visible when another WB is activated and then a second copy of it
appears when switching back to the original WB.

Module1

Private Sub Autpen()
Call CreateMyCustomToolbar
Worksheets("Database").Activate
End Sub

Public Sub CreateMyCustomToolbar()
Dim i As Long
Dim macro_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call Remove_ToolBars

Stuff to create tool bar goes here

End Sub

Sub Remove_ToolBars()
On Error Resume Next
Application.CommandBars("MyCustomToolbar").Delete
On Error GoTo 0
End Sub


[Sheet4(Database)Code]

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

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


[ThisWorkBook(Code)]

Private Sub Workbook_Activate()
Call CreateMyCustomToolbar
End Sub

Private Sub Workbook_Deactivate()
Call Remove_ToolBars
End Sub

 

[JLatham]

When I've set up custom menus for use in a workbook I've found this to work
nicely and without noticeable delay on even older systems without the speed
and power of today's CPUs:

In the Workbook_Open() event, don't do anything regarding menu control.
In the Workbook_Activate() event I create the menu
In the Workbook_Deactivate() event I destroy the menu
and because I usually have separate menus for various sheets:
in the Workbook_SheetActivate() event I go through my create menus process
in the Workbook_SheetDeactivate() I destroy the menu(s)

so perhaps by deleting the menus instead of just hiding them, you may cure
the problem.

 

[Project Mangler]

Hi Slim,

What I can't understand is how you can add a second toolbar with the same
name (if I understand your explanation correctly). That fails here with
"runtime error 5: Invalid procedure call or argument"

In your delete code if you do something like:
Dim cbar As CommandBar
For Each cbar In Application.CommandBars
If cbar.Name = "MyCustomToolbar" Then cbar.Delete
Next

rather than use the error handler do you get any errors?

 

[Slim Slender]

What I’m gathering from your reply, JLatham, is that looking beyond my
current situation to ones in which I would have more than one custom
commandbar in a Workbook the approach would be to create each
Worksheet specific commandbar in the Worksheet_Activate event of the
Worksheet rather than in a Workbook level Module, and then delete each
Worksheet specific commandbar with the Worksheet_Deactivate rather
than just hiding them. So all I would need in my current situation is
two procedures, plus one to delete all custom commandbars in the
Workbook in the Workbook_Deactivation event. Correctamundo? ‘Mangler,
could you modify your commandbar delete procedure to be more general,
that is, to delete all copies of all custom commandbars in a Workbook
upon Deactivation?

 

[Project Mangler]

Hi Slim,

Poking around in help

Sub DelBars()
Dim bar As CommandBar
For Each bar In Application.CommandBars
If Not bar.BuiltIn Then bar.Delete
Next
End Sub

Not extensively tested



What I’m gathering from your reply, JLatham, is that looking beyond my
current situation to ones in which I would have more than one custom
commandbar in a Workbook the approach would be to create each
Worksheet specific commandbar in the Worksheet_Activate event of the
Worksheet rather than in a Workbook level Module, and then delete each
Worksheet specific commandbar with the Worksheet_Deactivate rather
than just hiding them. So all I would need in my current situation is
two procedures, plus one to delete all custom commandbars in the
Workbook in the Workbook_Deactivation event. Correctamundo? ‘Mangler,
could you modify your commandbar delete procedure to be more general,
that is, to delete all copies of all custom commandbars in a Workbook
upon Deactivation?

 

[Slim Slender]

Thanks, I'll test your code. One more thing: What is the significance
of the bit that says Temporary:= True? Should I have that in my code
somewhere?

 

[Project Mangler]

Hi Slim,

I'm sticking my neck out here as I've not used this argument but it looks to
me like it means that the commandbar will be deleted when Excel closes.

However a little playing around means it looks like the bars are only
deleted after the application closes, not just a workbook.