Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate command bar control after opening 2nd workbook
All:
I use the following VBA code in the Workbook_Open procedure to create a new dropdown menu named "TASS". Set HelpMenu = Application.CommandBars(1).FindControl(ID:=30010) If HelpMenu Is Nothing Then Set myMenu = Application.CommandBars(1).Controls.Add _ (Type:=msoControlPopup, Temporary:=True) Else Set myMenu = Application.CommandBars(1).Controls.Add _ (Type:=msoControlPopup, Befo=HelpMenu.Index, Temporary:=True) End If With myMenu .Caption = "&TASS" End With My problem is that everytime I open another Excel Workbook that has this code, another identical "TASS" menu is created. I would like to avoid this annoyance by somehow checking if the "TASS" menu already exists before adding it. Can anybody suggest a quick and simple way that I can check if this menu already exists so that I don't add it again? Respectfully, Charles |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate command bar control after opening 2nd workbook
Hi
You need to insert this Set cbWSMenuBar = CommandBars("Worksheet Menu Bar") On Error Resume Next cbWSMenuBar.Controls("&TASS").Delete On Error GoTo 0 The on error bit is there just incase the menu bar isn't there. You should also delete the menu bar in your Workbook_Beforeclose event or it will be there twice when you open excel again Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cbWSMenuBar As CommandBar On Error Resume Next 'Incase it has already been deleted Set cbWSMenuBar = CommandBars("Worksheet Menu Bar") cbWSMenuBar.Controls("&TASS").Delete Set cbWSMenuBar = Nothing End Sub regards Paul On Mar 23, 8:16 am, Charles in Iraq wrote: All: I use the following VBA code in the Workbook_Open procedure to create a new dropdown menu named "TASS". Set HelpMenu = Application.CommandBars(1).FindControl(ID:=30010) If HelpMenu Is Nothing Then Set myMenu = Application.CommandBars(1).Controls.Add _ (Type:=msoControlPopup, Temporary:=True) Else Set myMenu = Application.CommandBars(1).Controls.Add _ (Type:=msoControlPopup, Befo=HelpMenu.Index, Temporary:=True) End If With myMenu .Caption = "&TASS" End With My problem is that everytime I open another Excel Workbook that has this code, another identical "TASS" menu is created. I would like to avoid this annoyance by somehow checking if the "TASS" menu already exists before adding it. Can anybody suggest a quick and simple way that I can check if this menu already exists so that I don't add it again? Respectfully, Charles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening a workbook withAuto-open Macros without transferring control | Excel Programming | |||
duplicate data, if command in VBA | Excel Programming | |||
How to control worksheet initialization upon opening a workbook | Excel Worksheet Functions | |||
How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
How to make opening of workbook conditional of opening of another workbook | Excel Programming |