Prev: Restricting pasting, except for Paste Values
Next: A formula for subtotals against each category
From: Slim Slender on 17 Apr 2010 22:39
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 Auto_Open()
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
From: JLatham on 18 Apr 2010 09:15
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.

"Slim Slender" wrote:

> 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 Auto_Open()
> 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
> .
>
From: Project Mangler on 18 Apr 2010 09:52
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" <johnscpierce(a)yahoo.com> wrote in message
news:d1c493f4-3a67-4240-aedb-f0476249a1ac(a)x7g2000vbc.googlegroups.com...
> 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 Auto_Open()
> 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


From: Slim Slender on 18 Apr 2010 11:17
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?
From: Project Mangler on 18 Apr 2010 12:54
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



"Slim Slender" <johnscpierce(a)yahoo.com> wrote in message
news:b17187f6-044e-4eea-8227-9f0264569742(a)f13g2000vbl.googlegroups.com...
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?


 |  Next  |  Last
Pages: 1 2
Prev: Restricting pasting, except for Paste Values
Next: A formula for subtotals against each category