Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel opens two worksheets instead of one | Excel Discussion (Misc queries) | |||
Alt E no longer opens Edit menu | Excel Discussion (Misc queries) | |||
Alt+E No Longer Opens Edit menu | Excel Discussion (Misc queries) | |||
Excel file opens but no worksheets visible | Excel Discussion (Misc queries) | |||
add a menu bar when document opens | Excel Programming |