Hi,
I have a written a code to create a menu during workbook open event.
The AddMenus is called during Workbook Open event
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
'(1)Delete any existing one. We must use On Error Resume next _ in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Menu").Delete
On Error GoTo 0
'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
'(3)Return the Index number of the Help menu. We can then use this to place a custom menu before.
iHelpMenu = cbMainMenuBar.Controls("Help").Index
'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
'(5)Give the control a caption
cbcCutomMenu.Caption = "&Menu"
'(6)Working with our new Control, add a sub control and give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Get Well Information"
.OnAction = Module1.CreateWorkBook
End With
'(6a)Add another sub control give it a Caption and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
' .OnAction = "MyMacro2"
End With
End Sub
Now what happens is when the menu is created Module1.CreateWorkbook is called (.OnAction = Module1.CreateWorkBook), but ideally it should be called after the menu created and then when i click the menu item "Get Well Information"..
Do u i have add any special event for onclick of menu item "Get Well Information" ?
Bookmarks