Adding to Right-Click Context Menu

[FlossHoss]

I'm not sure if this is complicated or if I am making it complicated in my mind.

Objective: I would like to add a new item on the right-click context menu in excel. When this new item is clicked I would like it to open up a designated user form.


So far I have figured out how to open a user form by overriding the right click menu completely and opening the user form. However, I want the user form to open only when selected on the right click menu.


Is this complicated? If anyone has idea, guidance, or the solution that would be helpful.

[Bob Phillips]

This is how you add it

    With Application.CommandBars("Cell")
        With .Controls.Add(Type:=msoControlButton)
            .BeginGroup = True
            .Caption = "Routine1"
            .OnAction = "Another"
        End With
    End With

and to remove it

    With Application.CommandBars("Cell")
        .Controls("Routine1").Delete
    End With

[FlossHoss]

Thank you!! It works great!

I'll post my code as well to document it. Thank you again!

[FlossHoss]

Since I wanted the Right-click item to appear in the entirety of the workbook I used Bob Phillips code as follows

Private Sub Workbook_Open()

With Application.CommandBars("Cell")
        With .Controls.Add(Type:=msoControlButton)
            .BeginGroup = True
            .Caption = "Dashboard"
            .OnAction = "Another"
        End With
    End With

End Sub

To remove the item after excel closes, so the items don't continually build up, I used the "remove it" segment of code as follows

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application.CommandBars("Cell")
        .Controls("Dashboard").Delete
    End With
    
End Sub

[FlossHoss]

Also, the idea was to open a user form from the new item on the right click menu. Bob Phillips' "add it" segment of code calls the macro .OnAction = "Another"

I created a module and added the following code

Sub Another()
'this is the user form I wanted to open
frmDashboard.Show
End Sub