Before Right Click event

M

mohsinb

Hello,

The BeforeRightClick event first fires the SelectionChange event. I
dont want the code in Worksheet_SelectionChange to run on
BeforeRightClick. Is there a way not to run the
worksheet_SelectionChange on right click ?

Thanks in advance.
 
R

Rob van Gelder

Both events fire. BeforeRightClick sits in queue until it's allowed to
execute.

This code may help for understanding:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeRightClick"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "SelectionChange"
End Sub
 
M

mohsinb

Hi,

Thanks for the quick response.


If the selection changes and the row changes then I check for some key
values in the previously selected row and if the previous row fails the
edits then the user is put back in the previous row to make corrections
or delete the row. This is to prevent them from entering rows without
filling in the key values. The previous rownumber is now set to the
active rownumber.

If the user changes selecion but not the row then no edits are done,
hoping that the user may comeback to the key columns after making
entries in non-key columns in the same row.

Right-click allows the user to insert a row(insertbutton on a form). On
a right-click on the same row - Selectionchange is fired but no edits
are done because they are in the same row. Now if I put the same
edits(before load form) in the WorkSheet_BeforeRightCLick it would
solve theproblem for the same row right-click, but the edits get
executed twice with the error messages being displayed twice when the
right-click is on a different row.

I hope the above is not too convoluted.

Thanks again.
 
P

Patrick Molloy

You can't stop the selection change event firing without
using
Application.EnableEvents = False
However, as the method implies, it stops all events.

What you can do though, is to set a boolean that stops
the selection change code from running...

Option Explicit
Private bStop As Boolean
Private Sub Worksheet_BeforeRightClick(ByVal Target As
Range, Cancel As Boolean)
bStop = Not bStop
Range("A1")= bstop
MsgBox "BeforeRightClick - Flag is " & bStop
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
If Not bStop Then
MsgBox "SelectionChange"
End If
End Sub

each time you select a cell, the selection change fires.
If you right clight, you'll set the flag. selecting
another cell won't show the selection change message.
right click will reset th eflag, and changing cells will
show the message again. As an indicator, the right-click
event also places the flag value in cell A1


HTH
Patrick Molloy
Microsoft Excel MVP
 
M

mohsinb

Rob,

Just to clarify further, is there a way of knowing that the
selectionchange is being fired by a right-click ?. if i could trap that
then I could arrange my logic.
 
R

Rob van Gelder

Firstly, I don't know how to get around the Event ordering issue. Personally
think there is no way, but I've been proven wrong before (tonight in fact!)

Basically you are trying to force a type of data validation. I think that
the same validation (and error) should apply even if the user right-clicks.

When I've done row level data entry, I create a userform for all the data
entry and create 4 buttons: Add, Edit, Copy, Delete. Then Lock/Protect the
worksheet.
Add opens the userform blank
Edit opens the userform with row contents, but will save overtop the current
row
Copy opens the userform with row contents, but will save as a new row.
Del deletes the row

All data validation is done in the userform.

This approach works well for me because each row update (when the userform
has it's save button clicked) can include a SQL database transaction.

I'm sorry, I don't have the easy answer.
 
R

Rob van Gelder

In a Module:
Public Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As
Integer

In the Worksheet:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox "BeforeRightClick"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not CBool(GetKeyState(vbKeyRButton) And &H8000) Then _
MsgBox "SelectionChange"
End Sub
 
M

mohsinb

Thanks Patrick. Unfortunately your suggestion doesnt work with what
need.

Rob, I should have gone the route that you have suggested. Maybe fo
next time. I will just have to bend my requirements a little for thi
spreadsheet. Invested a lot of time already to start new. Thanks
 
M

mohsinb

Rob,

Your last post solves my problem. WORKS GREAT. Just curious to know how
it works, if its possible to explain in simpler terms.

Thanks again.
 
Top