new menu opens in all worksheets
 

Using the following code, I fiind that the new menu appears in every
excel worksheet that is opened instead of appearing only in the
worksheet it belongs to. I've placed the code in the "ThisWorkbook"
module.
What can I do about this? I'd like this menu to appear only in it's
own worksheet.
Thanks in advance.
-----------

Private Sub Workbook_Open()
Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl

Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup,
temporary:=True) 'adds a menu item to the Menu Bar
With cmbControl
.Caption = "&NEW MENU" 'name of the menu
With .Controls.Add(Type:=msoControlButton)
.Caption = "Calculation1"
.OnAction = "Macro1"
End With
With .Controls.Add(Type:=msoControlButton) 'adds a
dropdown button to the menu item
.Caption = "Tables"
.OnAction = "Macro2"
End With
With .Controls.Add(Type:=msoControlButton) 'adds a
dropdown button to the menu item
.Caption = "Create New Sheet"
.OnAction = "Macro3"
End With

End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'in case the menu item has already been
deleted
Application.CommandBars("Worksheet Menu Bar").Controls("NEW
MENU").Delete 'delete the menu item
End Sub

new menu opens in all worksheets
 

Put the code in the Worksheet_Activate and Worksheet_Deactivate events.

Mike F
"dolphin" wrote in message
...
Using the following code, I fiind that the new menu appears in every
excel worksheet that is opened instead of appearing only in the
worksheet it belongs to. I've placed the code in the "ThisWorkbook"
module.
What can I do about this? I'd like this menu to appear only in it's
own worksheet.
Thanks in advance.
-----------

Private Sub Workbook_Open()
Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl

Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup,
temporary:=True) 'adds a menu item to the Menu Bar
With cmbControl
.Caption = "&NEW MENU" 'name of the menu
With .Controls.Add(Type:=msoControlButton)
.Caption = "Calculation1"
.OnAction = "Macro1"
End With
With .Controls.Add(Type:=msoControlButton) 'adds a
dropdown button to the menu item
.Caption = "Tables"
.OnAction = "Macro2"
End With
With .Controls.Add(Type:=msoControlButton) 'adds a
dropdown button to the menu item
.Caption = "Create New Sheet"
.OnAction = "Macro3"
End With

End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'in case the menu item has already been
deleted
Application.CommandBars("Worksheet Menu Bar").Controls("NEW
MENU").Delete 'delete the menu item
End Sub