Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet toolbar
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. "Slim Slender" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet with its own toolbar | Excel Programming | |||
Forms toolbar Worksheet | Excel Discussion (Misc queries) | |||
Keep Custom Toolbar with Worksheet | Excel Programming | |||
How do I put a toolbar button into a worksheet? | Excel Worksheet Functions | |||
Keep toolbar from opening in worksheet | Excel Discussion (Misc queries) |