BeforeRightClick() and Cell menu

[John A Grandy]

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 !

 

[Rob Bovey]

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 *

 

[Jim Rech]

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

 

[John A Grandy]

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