Menu with Multiple Popups

[helpexcel]

Hi - I've been racking my brain with this one and tried a million different things but just can't get it sorted. I have 2 popup menu items, that I want shown in the main menu. However it only shows one in the main menu and then the other is shown in the first popup. What am I missing?? thanks!

Sub CustomMenu()

Dim iMenu As CommandBarControl
Dim iMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim iCustomMenu As CommandBarControl

On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Menu").Delete
On Error GoTo 0

Set iMenuBar = Application.CommandBars("Worksheet Menu Bar")

iHelpMenu = iMenuBar.Controls("Help").Index

Set iCustomMenu = iMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
With iCustomMenu
    .Caption = "Menu"

Set iCustomMenu = iCustomMenu.Controls.Add(Type:=msoControlPopup)
With iCustomMenu
    .Caption = "Soup"
    .BeginGroup = True

    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Chowder"
        .OnAction = "NewEngland"
        .Enabled = True
        .FaceId = 1
    End With
    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Stew"
        .OnAction = "Irish"
        .Enabled = True
        .FaceId = 2
    End With
    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Escarole"
        .OnAction = "Italt"
        .Enabled = True
        .FaceId = 3
    End With
    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "French Onion"
        .OnAction = "France"
        .Enabled = True
        .FaceId = 4
    End With
End With
    
Set iCustomMenu = iCustomMenu.Controls.Add(Type:=msoControlPopup)
With iCustomMenu
    .Caption = "Salad"
    .BeginGroup = True

    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Caesar"
        .OnAction = "Mexico"
        .Enabled = True
        .FaceId = 1
    End With
    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Wedge"
        .OnAction = "Jersey"
        .Enabled = True
        .FaceId = 2
    End With
    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Southwest"
        .OnAction = "Arizona"
        .Enabled = True
        .FaceId = 3
    End With
    With iCustomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = "Kale"
        .OnAction = "California"
        .Enabled = True
        .FaceId = 4
    End With
End With
End With
End Sub

 

[Dan_W]

I suspect it's because you insist on calling everything ICustomMenu.
I have renamed two the two submenus, and adjust the code slightlyl. The following code should work.
Separately, it would help others in helping you in future (should you need it) if you could please update your details with your version of Excel. The code below suggests that you're using an older version of Excel (2007?).

Sub CustomMenu()

    Dim iMenu As CommandBarControl
    Dim iMenuBar As CommandBar
    Dim iHelpMenu As Integer
    Dim iCustomMenu As CommandBarControl
    Dim iCustomMenuOne As Object
    Dim iCustomMenuTwo As Object

    On Error Resume Next
        Application.CommandBars("Worksheet Menu Bar").Controls("Menu").Delete
    On Error GoTo 0
    
    Set iMenuBar = Application.CommandBars("Worksheet Menu Bar")
    
    iHelpMenu = iMenuBar.Controls("Help").Index
    
    Set iCustomMenu = iMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
    With iCustomMenu
        .Caption = "Menu"
    
        Set iCustomMenuOne = iCustomMenu.Controls.Add(Type:=msoControlPopup)
        With iCustomMenuOne
            .Caption = "Soup"
            .BeginGroup = True
        
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Chowder"
                .OnAction = "NewEngland"
                .Enabled = True
                .FaceId = 1
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Stew"
                .OnAction = "Irish"
                .Enabled = True
                .FaceId = 2
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Escarole"
                .OnAction = "Italt"
                .Enabled = True
                .FaceId = 3
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "French Onion"
                .OnAction = "France"
                .Enabled = True
                .FaceId = 4
            End With
        End With
        
        Set iCustomMenuTwo = iCustomMenu.Controls.Add(Type:=msoControlPopup)
        With iCustomMenuTwo
            .Caption = "Salad"
            .BeginGroup = True
        
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Caesar"
                .OnAction = "Mexico"
                .Enabled = True
                .FaceId = 1
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Wedge"
                .OnAction = "Jersey"
                .Enabled = True
                .FaceId = 2
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Southwest"
                .OnAction = "Arizona"
                .Enabled = True
                .FaceId = 3
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Kale"
                .OnAction = "California"
                .Enabled = True
                .FaceId = 4
            End With
        End With
    End With
End Sub

 

[helpexcel]

Ah, I tried to change that, but used something like this...
Also I'm using Excel 365.

Set iCustomMenuTwo = iCustomMenuTwo.Controls.Add(Type:=msoControlPopup)
        With iCustomMenuTwo

 

[Dan_W]

Which is why it became a nested control within itself.
If you're using Excel 365, then these menu items are appearing in the Ribbon under the AddIns tab, are they not? You code is quite old then, because what it is trying to do is add a new menu item to the grey deprecated menu bar circa Excel 2007.

Would you not prefer a custom ribbton tab instead? They're easy enough to make, if you'd like some help.

 

[helpexcel]

yep, the menu appears on the AddIns tab. I'm curious about the ribbon tab, and would absolutely appreciate the help. Don't have a clue where to start with that. Assuming you can add it when the workbook opens and delete it when it closes?

 

[Dan_W]

I am so sorry - I only just stumbled across this thread now looking for something else, and I see that I never responded to you. I can't apologise enough. I don't suppose you still need help with it, do you?