Solved

Differentiating between a single click, a double click and a right click in Excel

Posted on 2013-10-28
13
3,578 Views
Last Modified: 2013-10-28
Hi, I am trying to run three different macros when a user clicks on a cell, depending on whether the user has single clicked on the cell (using the left mouse button), double clicked on the cell (using the left mouse button), or right-clicked on the cell (using the right mouse button).

The problem is that all three mouse actions trigger the SelectionChange event, and this event is triggered first, before the BeforeDoubleClick event and before the BeforeRightClick event. I can trap for a right click inside the SelectionChange routine (see the code below). But I can't detect the difference between a left click and a double click.

Does anyone know how to do this? Perhaps with Windows API calls?

(I'm amazed that this exact problem hasn't been posted anywhere before, but I have searched and cannot find an answer. So apologies if I missed it).

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    MsgBox "Double click"
    Cancel = True
    
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    MsgBox "Right click"
    Cancel = True
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If KeyPressed(vbKeyRButton) = True Then Exit Sub
    MsgBox "Left click"
    Cancel = True
        
End Sub

Public Function KeyPressed(ByVal Key As Long) As Boolean
    
    'The function returns true if a key is pressed,false if not
    KeyPressed = CBool((GetAsyncKeyState(Key) And &H8000) = &H8000)
    
End Function
    

Open in new window

0
Comment
Question by:jwshome
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39605692
Why is the fact that SelectionChange comes first a problem?
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39605727
Agree with MartinLiss... why is this causing you an issue?

It seems like you should just be able to put your "doubleClick" code in the "BeforeDoubleClick" event, and your right click code in the BeforeRightClick event.

Is there some reason you feel that the click type has to be determined in selectionChange Event?

If you expand on what you are trying to do, might be able to help you use the existing events so that you can get the functionality you are working towards.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39605744
Hi,

I suppose your problem comes because when you double-click or right-click a cell that was not previously selected the Selection_Change takes precedence and then if you left click a selected cell, no event is being fired.

Regards
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39605758
@RGonzo1971 - Still not seeing how the order that the events are fired is an issue.

Selection_Change will fire first... but still not understanding why that is a problem.
The fact that Selection_Change fires, does not stop the other events from firing as well.

If you put the necessary code in the other events, you should be able to do whatever you need to do.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39605787
Hi,

for the right-click in a new cell no problem, First event Selection_change then Rightclick

for the double click in a new cell, It only recognize a left click, that means a Selection_Change

Regards
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39605929
ok... I see what you are saying.  Sorry to be slow in understanding.

There is no dedicated Mouse click for the a single mouse click.

Let suppose you wanted to take the following actions as an example

Single Click - Color the cell green
Double Click - Color the cell yellow
Right Click - color the cell red

Only work-around I see is to take advantage of the events that already exist.

For example if you double click, you would have to go through the SelectionChange Event and treat it as though it were a single click and process the code accordingly... then when you hit the doubleclick event, you would have to redo anything special that you wanted to do with the doubleclick event... even if that meant "undoing" something that had been accomplished in the SelectionChange Event.

so a double click would first color the cell green (Selection change), then recolor the cell yellow when the double click event kicks in.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jwshome
ID: 39606108
Thanks for all the comments. The example of the red/yellow/green is exactly right. The solution to have the doubleclick routine undo the selection change routine is clever, but in my case the actions are difficult to undo, and they are also time consuming (they involve the physical movement of machinery controlled over a serial port).

Windows can recognize a right click, so there must be a way for Excel to get access to the Windows API, isn't there?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39606145
Again let me ask, why is the fact that SelectionChange comes first a problem?
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39606391
You might try using Application.OnTime to schedule a response to a leftclick after say a 1 second delay. If a doubleclick or rightclick occurs in the meantime, then the leftclick event in the OnTime sub will not occur.

The following code goes in the code pane for the worksheet being watched. Note that it uses the code name for that worksheet, not the tab name.

As written, the code watches A1:A10 for either a leftclick, rightclick or doubleclick on a single cell. It then displays a message box naming the cell that was clicked and the type of click.
Dim bTrapped As Boolean
Dim cel As Range, rgWatch As Range

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(cel, Target) Is Nothing Then
    MsgBox "Doubleclick at " & cel.Address
    bTrapped = True
    Cancel = True
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(cel, Target) Is Nothing Then
    MsgBox "Rightclick at " & cel.Address
    bTrapped = True
    Cancel = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targ As Range
Set rgWatch = Range("A1:A10")
Set targ = Intersect(Target, rgWatch)
If Not targ Is Nothing Then
    If targ.Cells.Count = 1 Then
        bTrapped = False
        Set cel = targ.Cells(1, 1)
            'Allow 1 second for user to complete a rightclick or doubleclick before trapping leftclick
        Application.OnTime Now + 1 / 86400, "Sheet1.DelayedWatch"       'Note that Sheet1 is codename for worksheet (not tabname)
    End If
End If
End Sub

Private Sub DelayedWatch()
If bTrapped = False Then
    MsgBox "Leftclick at " & cel.Address
End If
End Sub

Open in new window

TrapLeftRightDoubleClickQ2827889.xlsm
0
 

Author Comment

by:jwshome
ID: 39606468
For MartinLiss:

The reason that SelectChange coming first is a problem is that when I enter the SelectChange routine I don't know whether I got there because the user clicked once on the left button of their mouse, or twice. I want to execute two completely different sets of code depending on whether the user clicked or double clicked, and I specifically do not want to execute the single-click code if the user double clicked.

In other words, what happens when the user double clicks is that the first click triggers the SelectChange routine before the second click can get recognized. I'm trying to figure out, in effect, how to recognize the second click from inside the SelectChange routine, in the same way that I am able to recognize the right click (which also triggers the SelectChange routine) from within the SelectChange routine.

Maybe another way to put it is this: in Excel, whenever you click the mouse on a new cell, whether you single click, or double click, or right click, the SelectChange routine is triggered first. The question I am asking is how can I figure out, from within the SelectChange routine, how to tell which of the three types of clicking got me there, so I can process each of the three differently.

All of the explanations on the internet are answering a much simpler question: "how can I trap a doubleclick"? The answer is easy - use the "BeforeDoubleClick" routine. And this works perfectly, unless you are also trying to trap for a single click - in which case the SelectChange routine is triggered first.

I hope this is helpful.
0
 

Author Comment

by:jwshome
ID: 39606507
To byundt,

I was preparing a condescending reply to explain how you fail to understand how events are processed in Excel when I took the time to understand your code. Brilliant solution. It works as described.

Would you know how to set up a delay of 500 milliseconds as opposed to a full second? It appears that the "Now" command is generating times that are rounded to the nearest second, so that even if I plug in a delay of 1/172800 the delay is sometimes shorter than half a second and sometimes longer due to rounding.

Thanks for your help.

(For anyone interested - I would still like to know how Windows determines if a click is single or double. They must use a similar logic to byundt's, but there must be a direct API call somewhere...)
0
 

Author Closing Comment

by:jwshome
ID: 39606511
Works exactly as indicated (and it is very clever by moving the single click execution out of the single click routine...)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39606521
Deleted due to byundt's more complete answer.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now