Custom Menu help

[sharky12345]

I've recently discovered how to create custom menus through VBA and have been playing around with code to see what I can do but I'm stuck on this one little issue - how can I adapt the following so that the 'Exit' menu, (the last one), appears as it's own menu and not as part of the sheets sub menu above?

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("&Data Tools").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 = "&Data Tools"
    '(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 = "Update Device Master List"
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .BeginGroup = True
        .Caption = "Search Tool"
        .OnAction = "MyMacro1"
        .FaceId = 172
    End With
    
      With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .BeginGroup = True
        .Caption = "Unknown User Devices"
        .OnAction = "MyMacro2"
    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)
        .BeginGroup = True
        .Caption = "Email Unused Devices"
        .OnAction = "MyMacro2"
    End With
    
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .BeginGroup = True
        .Caption = "Show All Devices"
        .OnAction = "MyMacro2"
    End With
    'Repeat step "6a" for each menu item you want to add.
 
    'Add another menu that will lead off to another menu
    'Set a CommandBarControl variable to it
    Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
    ' Give the control a caption
    cbcCutomMenu.Caption = "Shee&ts Menu"
    
         'Add a contol to the sub menu, just created above
    
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Summary"
        .FaceId = 71
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Master List"
        .FaceId = 72
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Device Data - 8 Week"
        .FaceId = 73
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Raw Data - 8 Week"
        .FaceId = 74
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Raw Data - 4 Month"
        .FaceId = 75
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Display Sheet Tabs"
        .FaceId = 213
        .OnAction = "MyMacro2"
    End With
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Hide Sheet Tabs"
        .FaceId = 214
        .OnAction = "MyMacro2"
    End With
    
     With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .BeginGroup = True
        .Caption = "E&xit"
        .OnAction = "MyMacro1"
    End With
                    
End Sub

 

[Andrew Poulsom]

Insert this line before the code that adds the Exit menu:

    Set cbcCutomMenu = Application.CommandBars("Worksheet Menu Bar").Controls("&Data Tools")

 

[sharky12345]

Thanks Andrew - so simple when you know how!