How do I add a new menu after the Help menu?
I can add to other menus but my attempts thus far at creating my own menu have been fruitless.
How do I add a new menu after the Help menu?
I can add to other menus but my attempts thus far at creating my own menu have been fruitless.
Hi,
Do you mean through code?
Sub AddMenu() Dim ComPop As CommandBarPopup Dim Combut As CommandBarButton Set ComPop = Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup) ComPop.Caption = "My New Menu" Set Combut = ComPop.Controls.Add(msoControlButton) Combut.Style = msoButtonCaption Combut.Caption = "Button 1" Combut.OnAction = "MyMacro" Set Combut = ComPop.Controls.Add(msoControlButton) Combut.Style = msoButtonCaption Combut.Caption = "Button 2" Combut.OnAction = "MyMacro" End Sub Sub MyMacro() MsgBox "You pressed " & Application.CommandBars.ActionControl.Caption End Sub
HTH
Dan
Hi SJ,
See if this helps:
Any problems - let me know.Const strMenuName As String = "&My Menu" Const strCap1 As String = "Cap1" Const strSub1 As String = "Sub1" Sub AddToWorksheetMB() Dim cbWMB As CommandBar Dim cbctrlCustom As CommandBarControl Dim HelpMenu As CommandBarControl DeleteFromWMB Set cbWMB = Application.CommandBars("Worksheet Menu Bar") On Error Resume Next Set HelpMenu = CommandBars(1).FindControl(Id:=30010) On Error GoTo 0 If HelpMenu Is Nothing Then Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup) 'Help doesn't exist so add to the end Else Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup, before:=HelpMenu.Index + 1) 'Add after Help End If With cbctrlCustom .Caption = strMenuName With .Controls.Add(Type:=msoControlButton) .Caption = strCap1 .OnAction = strSub1 End With 'add a menu item End With End Sub Sub DeleteFromWMB() Dim cbWMB As CommandBar Set cbWMB = Application.CommandBars("Worksheet Menu Bar") On Error Resume Next cbWMB.Controls(strMenuName).Delete End Sub Sub Sub1() MsgBox "Hello" End Sub
Perfect. Thanks.
Incidentally, how would you add a child menu?
And delete the thing?
Hi SJ,
OK, version2 adds a pop-up menu too. (The deletion routine was already in the original - "DeleteFromWMB"). Is this what you wanted?
HTHConst strMenuName As String = "&My Menu" Const strCap1 As String = "Cap1" Const strSub1 As String = "Sub1" Const strCap2 As String = "Cap2" Const strSub2 As String = "Sub2" Sub AddToWorksheetMB() Dim cbWMB As CommandBar Dim cbctrlCustom As CommandBarControl, cbPop As CommandBarPopup Dim HelpMenu As CommandBarControl DeleteFromWMB Set cbWMB = Application.CommandBars("Worksheet Menu Bar") On Error Resume Next Set HelpMenu = CommandBars(1).FindControl(Id:=30010) On Error GoTo 0 If HelpMenu Is Nothing Then Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup) 'Help doesn't exist so add to the end Else Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup, before:=HelpMenu.Index + 1) 'Add after Help End If With cbctrlCustom .Caption = strMenuName With .Controls.Add(Type:=msoControlButton) .Caption = strCap1 .OnAction = strSub1 End With 'add a menu item Set cbPop = .Controls.Add(msoControlPopup) With cbPop .BeginGroup = True .Caption = "Pop menu :" .Tag = "MyPop" With cbPop.Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = strCap2 .OnAction = strSub2 End With End With 'popup on main popup End With End Sub Sub DeleteFromWMB() Dim cbWMB As CommandBar Set cbWMB = Application.CommandBars("Worksheet Menu Bar") On Error Resume Next cbWMB.Controls(strMenuName).Delete End Sub Sub Sub1() MsgBox "Hello" End Sub Sub Sub2() MsgBox "Hello again!" End Sub
Excellent. I appreciate this.