Duplicate command bar control after opening 2nd workbook

[Charles in Iraq]

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, Before:=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

 

[paul.robinson]

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