VBA – Modify the Right-Click Menu to Call a Macro

Right-Click to Call a Macro

Here is some code that will allow a user to select your macro from the default menu that appears after they right click a cell.

1. Put the following code in the ThisWorkbook code window

Private Sub Workbook_Open()



Dim MyMenu As Object

    

Set MyMenu = Application.ShortcutMenus(xlWorksheetCell) _

    .MenuItems.AddMenu("This is my Custom Menu", 1)

     

With MyMenu.MenuItems

    .Add "MyMacro1", "MyMacro1", , 1, , ""

    .Add "MyMacro2", "MyMacro2", , 2, , ""

End With

      

Set MyMenu = Nothing



End Sub

2. Put the following code in a module

Public Sub mymacro1()

MsgBox "Macro1 from a right click menu"

End Sub



Public Sub mymacro2()

MsgBox "Macro2 from a right click menu"

End Sub

3. Close your workbook and re-open!