Excel VBA - Add submenu to custom right-click menu

Question

Long time viewer, first time poster. I have a form with Right-Click functions that work fine. I'm trying to add a sub menu to the main right-click menu to separate some functions/command. I need/want to insert the section where the 'Select Case' is, however, its only showing the top menu. Not sure where to go from here. Any help would be awesome

Thanks :)

P.S. I would be happy to explain further if needed.

Sub fzCopyPaste(iItems As Integer)
On Error Resume Next
CommandBars("Custom").Delete
Set PopBar = CommandBars.Add(Name:="Custom", Position:=msoBarPopup, MenuBar:=False, Temporary:=True)

'Add top_menu on Main Popbar : This work fine

Set top_menu = PopBar.Controls.Add(Type:=msoControlButton)
With top_menu
    '.FaceId =
    .Caption = "&Some Commands"
End With

Need to Insert the below sub menu(s) into the top menu But nothing shows up : Does not work

Select Case iItems
Case 1  ' Copy and Paste
    Set copy_button = top_menu.Controls.Add(Type:=msoControlButton)
    With copy_button
        .FaceId = 19
        .Caption = "&Copy"
        .Tag = "tCopy"
        .OnAction = "fzCopyOne(true)"
    End With

    Set paste_button = top_menu.Controls.Add(Type:=msoControlButton)
    With paste_button
        .FaceId = 22
        .Tag = "tPaste"
        .Caption = "&Paste"
        .OnAction = "fzCopyOne(true)"
    End With
Case 2 '  Paste Only
    Set paste_button = top_menu.Controls.Add(Type:=msoControlButton)
    With paste_button
        .FaceId = 22
        .Tag = "tPaste"
        .Caption = "&Paste"
        .OnAction = "fzCopyOne(true)"
    End With
End Select

'Extra top menue(s) below here : This work fine

    Set paste_button = PopBar.Controls.Add(Type:=msoControlButton)
    With paste_button
        .FaceId = 22
        .Tag = "tPaste"
        .Caption = "Main POP BAR 2"
        .OnAction = "fzCopyOne(true)"
    End With


PopBar.ShowPopup

CommandBars("Custom").Delete
End Sub

DayLove.01 · Accepted Answer · 2016-07-14 17:13:35Z

3

You set Copy_Button equal to an msoControlButton. If you want a button, this is correct. You want a menu though, so you should set it to an msoControlPopup. Try something like this:

Set Top_Menu = PopBar.Controls.Add(Type:=msoControlPopup)
With Top_Menu 
    .Caption = "&Some Commands"
    Set MySubMenu = .Controls.Add(Type:=msoControlPopup, before:=1, temporary:=True)
    Select Case iItems
        Case 1
            With MySubMenu
                .Caption = "Submenu Commands"
                With .Controls.Add(Type:=msoControlButton, before:=1, temporary:=True)
                    .FaceId = 19
                    .Caption = "&Copy"
                    .Tag = "tCopy"
                    .OnAction = "fzCopyOne(true)"
                End With

                With .Controls.Add(Type:=msoControlButton, before:=2, temporary:=True)
                    .FaceId = 22
                    .Tag = "tPaste"
                    .Caption = "&Paste"
                    .OnAction = "fzCopyOne(true)"
                End With
            End With
        Case 2
            'etc
    End Select
End With

I removed the “Top_Menu” section (first 3 lines) with the below; it was adding an extra button and then the desired menu.

Set MySubMenu = PopBar.Controls.Add(Type:=msoControlPopup, before:=1, temporary:=True)
With MySubMenu
    .Caption = "&Some Commands"

edited Jul 14, 2016 at 17:13

DayLove.01

801 silver badge7 bronze badges

answered Jul 14, 2016 at 15:42

Tim

2,9123 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DayLove.01 Over a year ago

Thanks @Tim for explaining that, works great. I made an edit though, but works great non-the-less.

Collectives™ on Stack Overflow

Excel VBA - Add submenu to custom right-click menu

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related