Trouble with modifying cell right-mouse-click context menu

[jlwilloz]

Question relates to modifying the right-mouse-click cell context menu in
Excel 2007

I am attempting to add a menu item to the cell context menu that pops up
upon clicking the right mouse button. I found a microsoft article here:
http://msdn.microsoft.com/en-us/library/bb211466.aspx that seems to explain
how to do this. Based on this article, I have inserted VBA code into the
worksheet of interest as follows:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
For Each icbc In Application.CommandBars("cell").Controls
If icbc.Tag = "RollJob" Then icbc.Delete
Next icbc

With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=6, _
temporary:=True)
.Caption = "Roll Job"
.OnAction = "RollJob"
.Tag = "RollJob"
End With

End Sub

Private Sub Worksheet_Deactivate()

For Each icbc In Application.CommandBars("cell").Controls
If icbc.Tag = "RollJob" Then icbc.Delete
Next icbc

End Sub

RollJob is a macro in a module within the same workbook.

When I right-click in this sheet, nothing in the context menu appears to
have changed. Can anyone figure out what I am doing wrong here?

 

[Barb Reinhardt]

Try running this when you want to reset the command bars back to the default
settings.

Sub ResetCommandBars()

Application.CommandBars("Cell").Reset
Application.CommandBars("Row").Reset
Application.CommandBars("Column").Reset

End Sub

 

[Chip Pearson]

I'm not sure why you are recreating the control each time the
BeforeRightClick event fires. Even still, the following code works for
me:

Private Sub Worksheet_BeforeRightClick( _
ByVal Target As Range, Cancel As Boolean)
Dim C As Office.CommandBarControl
Const C_TAG = "MyTag"
Set C = _
Application.CommandBars("Cell").FindControl(Tag:=C_TAG)
Do Until C Is Nothing
C.Delete
Set C = _
Application.CommandBars("Cell").FindControl(Tag:=C_TAG)
Loop
With Application.CommandBars("Cell").Controls.Add( _
Type:=msoControlButton, before:=6, temporary:=True)
.Caption = "Click Me"
.OnAction = "'" & ThisWorkbook.Name & "'!ClickMe"
.Tag = C_TAG
End With
End Sub


where "ClickMe" is a sub proc defined in a regular code module. I
would build the control once in the sheet's Activate event and tthen
enable it in Activate and disable it in the Deactivate event. Don't
put any code in the BeforeRightClick procedure.

Private Const C_TAG = "MyTag"

Private Sub Worksheet_Activate()
Dim C As Office.CommandBarControl
Set C = _
Application.CommandBars("Cell").FindControl(Tag:=C_TAG)
If C Is Nothing Then
With Application.CommandBars("Cell").Controls.Add( _
Type:=msoControlButton, before:=6, temporary:=True)
.Caption = "Click Me"
.OnAction = "'" & ThisWorkbook.Name & "'!ClickMe"
.Tag = C_TAG
End With
End If
C.Enabled = True
End Sub


Private Sub Worksheet_Deactivate()
Dim C As Office.CommandBarControl
Set C = Application.CommandBars("Cell").FindControl(Tag:=C_TAG)
If Not C Is Nothing Then
C.Enabled = False
End If
End Sub


This code creates the menu item the first time the sheet is activated,
and on subsequent activations simply enables tthe existing control.
When the sheet is deactivated, the control is disabled rather than
deleted. If you don't want to leave the control visible when the sheet
is deactivated, add

C.Visible = False

to the Deactivate event and

C.Visible = True

to the Activate event.


There is no reason to delete and recreate the control every time
BeforeRightClick runs. Create the ccontrol once and be done with it.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]