Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
Please explain why SelectionChange and BeforeRightClick are clashing?
"mohsinb" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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 "mohsinb" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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 -----Original Message----- 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. --- Message posted from http://www.ExcelForum.com/ . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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. --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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. "mohsinb" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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 "mohsinb" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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 -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
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. --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Right Click event
I'll point you to the documentation (reconstruct as one-line URL)
http://msdn.microsoft.com/library/en...etKeyState.asp "mohsinb" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Multi click Event possible? | Excel Discussion (Misc queries) | |||
ListBox Click Event | Excel Discussion (Misc queries) | |||
Click Event | Excel Programming | |||
Click event on Scrollbar | Excel Programming | |||
Mouse Click Event | Excel Programming |