Add menu item to right click

[Dev]

How can I add my own menu item to the right click context menu.

When user clicks on a cell and right clicks; I want my own menu item to be
seen - so that I can run a macro, when user clicks that menu and also wants
to disable that menu for some cells...

Any help is really appreciated!!

Thanks in advance!!

 

[Leith Ross]

Hello Dev,

Copy this code and paste it into a new VBA Module in your Workbook. Run
it once to add your menu item to the Right-Click menu. Besure to change
MyMacro and MyMenuCaption to what you want.


Code:
--------------------

Public Sub AddToContextMenu()

Dim C
Dim cmdNew As CommandBarButton
Dim MyMenuCaption As String

MyMenuCaption = "Call MyMacro" '<<<< Change this

'Don't add the Menu if it exists.
For Each C In Excel.CommandBars("cell").Controls
If C.Caption = MyMenuCaption Then
Exit Sub
End If
Next C

Set cmdNew = Excel.CommandBars("cell").Controls.Add
With cmdNew
.Caption = MyMenuCaption
.OnAction = "MyMacro name" '<<< Insert the name of your Macro
.BeginGroup = True
End With

End Sub

Public Sub RemoveFromContextMenu()

Dim MyMenuCaption As String

On Error Resume Next
MyMenuCaption = "Call MyMacro" '<<<< Change this
With CommandBars("cell").Controls(MyMenuCaption)
.BeginGroup = False
.Delete
End With

End Sub

 

[Dev]

Thanks Leith for your reply. This worked!!