Run Macros From Context Menu

[alexfooty]

Hi
If I have two macros, Macro1 & Macro2, is it possible to right click on any sheet in the workbook and run these macros from the context menu?

 

[t0ny84]

Hi alexfooty,

You can find the code needed on - Modify the Right-Click Menu to Call a Macro - VBA Code Examples
Just need to modify to suit your macros.

Adding the below code to the VBA Editor then running it will reset the context menu.

Sub ResetContextMenu ()
Application.Commandbars("Cell").Reset
End Sub

t0ny84

 

[alexfooty]

Hi T0ny84
Thanks for your quick reply
Getting syntax error ".MenuItems.AddMenu("This is my Custom Menu", 1)"
Is it something I need to modify?

 

[mehidy1437]

In my experience, it's better to use macro in customized ribbon rather than using in right button, because some times macro don't show up in contexmenu for all files, just sharing my experience.

 

[t0ny84]

Hi T0ny84
Thanks for your quick reply
Getting syntax error ".MenuItems.AddMenu("This is my Custom Menu", 1)"
Is it something I need to modify?

I just tried this and it worked, try this and let me know if you have any issues.

Put the following into the ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Removes the added menu when the workbook is closed.
Application.CommandBars("Cell").Reset
End Sub

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 "NameWantInMenu", "MacroName", ,OrderNumber, , ""
 .Add "MyMacro1", "MyMacro1", , 1, , ""
 .Add "MyMacro2", "MyMacro2", , 2, , ""
End With
Set MyMenu = Nothing
End Sub

On 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

Save the workbook as a Macro Enabled Workbook then close and reopen.
I changed: .Add "MyMacro1", "MyMacro1", , 1, , ""
To: .Add "Bom", "MyMacro1", , 1, , ""

 

[t0ny84]

To reset a specific menu you can use below, please note that they will also remove any other customization you have to the menu

Right Click On A Cell
Application.CommandBars("Cell").Reset
Application.CommandBars("cell").Enabled = True

Right Click On A Column
Application.CommandBars("Column").Reset
Application.CommandBars("column").Enabled = True

Right Click On A Command Bar
Application.CommandBars("Row").Reset
Application.CommandBars("row").Enabled = True

 

[alexfooty]

t0ny84 - Thank you so much for your help with this - it works perfectly. Thank you