Add macro to context menu

Add macro to context menu

October 11, 2021 0 Door Bjorn Meijer

If you often use a certain macro, it can be useful to add it to the context menu (the menu that pops up when you right-click on a cell).

To do this, open the VBA editor and place the code below in ThisWorkbook.

Private Sub Workbook_Open() Dim MyContextMenu As Object Set MyContextMenu = Application.ShortcutMenus(xlWorksheetCell) _ .MenuItems.AddMenu("My Custom Menu Item", 1) With MyContextMenu.MenuItems .Add "Swap Two Cells", "SwapTwoCells", 1, , "" .Add "My macro 2", "MyMacro2", , 2, , "" End With Set MyContextMenu = Nothing End Sub

The above code creates a menu item on the top line labeled “My Custom Menu Item”. Then two macros are added within this menu item:

  • SwapTwoCells (shown as Swap Two Cells);
  • MyMacro2 (displayed as My macro 2).

Place the code below in a module or below the code above.
The SwapTwoCells function causes the values from two selected cells to be swapped with each other.

Macro2 displays a message box with the message “Macro2 from a context menu”.

Sub SwapTwoCells() Dim sHolder As String If Selection.Cells.Count = 2 Then With Selection sHolder = .Cells(1).formula If .Areas.Count = 2 Then ' Cells selected using Ctrl key .Areas(1).formula = .Areas(2).formula .Areas(2).formula = sHolder Else ' Adjacent cells are selected .Cells(1).formula = .Cells(2).formula .Cells(2).formula = sHolder End If End With Else MsgBox "Select only TWO cells to swap", vbCritical End If End Sub Public Sub mymacro2() MsgBox "Macro2 from a context menu" End Sub

Close your Excel document and save it. As soon as you open it again, an extra menu item has been added to your context menu.