VBA Submenu and sort order

[bsquad]

I am wondering if the following code can somehow be altered so the Submenu is at the top or above 'Copy'

Sub Submenu()
    Dim Bar As CommandBar
    Dim NewMenu As CommandBarControl
    Dim NewSubmenu As CommandBarButton
    DeleteSubmenu
    Set Bar = CommandBars("Cell")
    Set NewMenu = Bar.Controls.Add(Type:=msoControlPopup, temporary:=True)
    NewMenu.Caption = "&Functions"
    NewMenu.BeginGroup = True
    Set NewSubmenu = NewMenu.Controls.Add(Type:=msoControlButton)
    With NewSubmenu
        .FaceId = 266
        .Caption = "I&mport"
        .OnAction = "CompAggregation"
    End With
    Set NewSubmenu = NewMenu.Controls.Add(Type:=msoControlButton)
    With NewSubmenu
        .FaceId = 254
        .Caption = "&Row Select"
        .OnAction = "Jump"
    End With


End Sub

 

[Domenic]

When creating your new menu, you can use the Before argument to specify the position of the control on the command bar. Then you can set the BeginGroup property to True for the second control on the command bar.

Sub Submenu()
    Dim Bar As CommandBar
    Dim NewMenu As CommandBarControl
    Dim NewSubmenu As CommandBarButton
    DeleteSubmenu
    Set Bar = CommandBars("Cell")
    [COLOR=#ff0000]Set NewMenu = Bar.Controls.Add(Type:=msoControlPopup, [B]Before:=1[/B], temporary:=True)[/COLOR]
    NewMenu.Caption = "&Functions"
    [COLOR=#ff0000]Bar.Controls(2).BeginGroup = True[/COLOR]
    Set NewSubmenu = NewMenu.Controls.Add(Type:=msoControlButton)
    With NewSubmenu
        .FaceId = 266
        .Caption = "I&mport"
        .OnAction = "CompAggregation"
    End With
    Set NewSubmenu = NewMenu.Controls.Add(Type:=msoControlButton)
    With NewSubmenu
        .FaceId = 254
        .Caption = "&Row Select"
        .OnAction = "Jump"
    End With
End Sub

 

[bsquad]

Domenic,

thank you for the reply and a resolution; it working well.
>>I knew it was going to be something simple.