BeforeRightClick() and Cell menu
 

i trap the Worksheet_BeforeRightClick() event and modify the
Application.CommandBars("Cell") menu, making some of the built-in
CommandBarControls invisible and adding some new CommandBarControls ....

this technique works fine (right-clicks bring up the customized menu) EXCEPT
if a range is selected on the sheet and the right-click is on a cell in the
selected range: in this case, the default Cell menu shows up ...

the Worksheet_BeforeRightClick() event is still being triggered, and i can
still single-step through the code which modifies the Cell menu with no
errors ... programatically, nothing occurs which is any different than the
no-selection case ... but the Cell menu is unchanged from its default state
!

very bizarre behavior !

BeforeRightClick() and Cell menu
 

Hi John,

Just curious why you're modifying the default popup menu instead of
creating your own and substituting it for the built in menu. This would
solve the problem entirely.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"John A Grandy" wrote in message
...
i trap the Worksheet_BeforeRightClick() event and modify the
Application.CommandBars("Cell") menu, making some of the built-in
CommandBarControls invisible and adding some new CommandBarControls ....

this technique works fine (right-clicks bring up the customized menu)
EXCEPT
if a range is selected on the sheet and the right-click is on a cell in
the
selected range: in this case, the default Cell menu shows up ...

the Worksheet_BeforeRightClick() event is still being triggered, and i can
still single-step through the code which modifies the Cell menu with no
errors ... programatically, nothing occurs which is any different than
the
no-selection case ... but the Cell menu is unchanged from its default
state
!

very bizarre behavior !

BeforeRightClick() and Cell menu
 

I haven't run into this but then I believe you're not really using the
before_right_click event in the intended way. The idea is not to customize
the Cell menu real-time, but to display your own popup commandbar. An
example in case you want to change course:

In your startup code create a custom popup:

Sub MakePopup()
On Error Resume Next
CommandBars("MyCell").Delete
On Error GoTo 0
With CommandBars.Add("MyCell", msoBarPopup, False, True)
.Controls.Add msoControlButton, 19 ''Built-in Copy command
With .Controls.Add(msoControlButton, 1)
.Caption = "My Custom Menu item"
.OnAction = "DoBeep"
End With
End With
End Sub


Sub DoBeep()
Beep
End Sub

And then the event handler can show it:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True ''Kill Excel's Cell menu
CommandBars("MyCell").ShowPopup ''Show ours
End Sub



--
Jim Rech
Excel MVP

BeforeRightClick() and Cell menu
 

ahhh .... i see .... ok, i'll do that

"Jim Rech" wrote in message
...
I haven't run into this but then I believe you're not really using the
before_right_click event in the intended way. The idea is not to
customize
the Cell menu real-time, but to display your own popup commandbar. An
example in case you want to change course:

In your startup code create a custom popup:

Sub MakePopup()
On Error Resume Next
CommandBars("MyCell").Delete
On Error GoTo 0
With CommandBars.Add("MyCell", msoBarPopup, False, True)
.Controls.Add msoControlButton, 19 ''Built-in Copy command
With .Controls.Add(msoControlButton, 1)
.Caption = "My Custom Menu item"
.OnAction = "DoBeep"
End With
End With
End Sub


Sub DoBeep()
Beep
End Sub

And then the event handler can show it:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True ''Kill Excel's Cell menu
CommandBars("MyCell").ShowPopup ''Show ours
End Sub



--
Jim Rech
Excel MVP