View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
jlwilloz jlwilloz is offline
external usenet poster
 
Posts: 1
Default Trouble with modifying cell right-mouse-click context menu

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 he
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, befo=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?