Add macro to context menu
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.