Adding New Menu

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?

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:

Code:

---

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

---

Any problems - let me know. ;)

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?

Code:

---

Const 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

---

HTH

Excellent. I appreciate this.