Consulting

Results 1 to 7 of 7

Thread: Solved: Adding New Menu

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

    Solved: 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?

    [VBA]
    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
    [/VBA]

    HTH
    Dan

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

    See if this helps:[vba]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[/vba]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?
    [vba]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[/vba]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
  •