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!
October 3rd, 2008 at 7:12 pm
I’m trying to utilize this, but when I close the workbook, and reopen it to test the workbook_open functionality, I get ANOTHER menu… and then another, creating a duplicate menu each time until I completely close that instance of Excel and start a new one fresh. How can I delete the menu from the application on workbook close?
Also, I’ve noticed that since it is on the application level, the menus are available within other workbooks under the same instance of Excel, making custom commands for a given workbook show up under a different workbook. Imagine have commands present from 2 workbooks simultaneously…
Any way I can delete menuitems once created? Or limit their scope in any way?