Consulting

Results 1 to 7 of 7

Thread: Adding New Menu

  1. #1
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location

    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.

  2. #2
    VBAX Regular
    Joined
    May 2004
    Location
    Sydney, Australia
    Posts
    36
    Location
    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

  3. #3
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi SJ,

    See if this helps:

    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.

  4. #4
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Perfect. Thanks.

  5. #5
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Incidentally, how would you add a child menu?
    And delete the thing?

  6. #6
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi SJ,

    OK, version2 adds a pop-up menu too. (The deletion routine was already in the original - "DeleteFromWMB"). Is this what you wanted?

    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

  7. #7
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Excellent. I appreciate this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •