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.
Dit delen:
- Klik om te delen met Twitter (Wordt in een nieuw venster geopend)
- Klik om te delen op Facebook (Wordt in een nieuw venster geopend)
- Meer
- Klik om dit te e-mailen naar een vriend (Wordt in een nieuw venster geopend)
- Klik om op LinkedIn te delen (Wordt in een nieuw venster geopend)
- Klik om te delen met Reddit (Wordt in een nieuw venster geopend)
- Klik om op Tumblr te delen (Wordt in een nieuw venster geopend)
- Klik om te delen op Telegram (Wordt in een nieuw venster geopend)
- Klik om te delen op WhatsApp (Wordt in een nieuw venster geopend)