Go Premium for a chance to win a PS4. Enter to Win


Trap left mouse click in Excel when clicking on a cell

Posted on 2014-01-20
Medium Priority
1 Endorsement
Last Modified: 2014-01-21
I've searched for many hours on this one.  For some reason there is a "BeforeRightClick" routine for excel worksheets, but none for Left click.  I have found many very detailed examples for using an API call for  GetAsyncKeyState with (vbKeyLButton) and other buttons.  None of them work for me.  I am using Excel 2013 and Windows 7.  The sample below will work to pop a message box for every other key pressed as indicated, EXCEPT for the LEFT mouse click, which is event I need to trap.  If no one has a solution, maybe they could suggest another way?  Here's my simple simple problem:  I have a macro to run when a user clicks on a cell.  I am using Worksheet_SelectionChange area now to simply trap when my certain cells have been selected, and fire my macro code.  This works, but the BIG problem is, when users are using the arrow keys to scroll across to see other columns, they are walking over the cells that then fire my code because they are "selecting" as they move over with the arrows and a macro runs when they touch my cell. I need the macro to ONLY run when a user LEFT CLICKS (a normal user function) on my cell, and NOT fire for any arrows (up, down, left, right), or any other inadvertent keypresses that would get to (land on) certain cells that I want to run a macro besides deliberate left click.  Below is what I have tried, several variations, including one last one where someone said they solved it by adding a bit mask - that example also didn't work for me:

OK the best code example works for every button click except LEFT:

Using this in a module:

Public Declare Function GetAsyncKeyState Lib "user32" _
        (ByVal vKey As Long) As Integer

and this in the worksheet_selectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        If GetAsyncKeyState(vbKeyShift) And &H8000 Then

            MsgBox "shift key pressed"

    ElseIf GetAsyncKeyState(vbKeyRight) And &H8000 Then

            MsgBox "right arrow pressed"

    ElseIf GetAsyncKeyState(vbKeyLeft) And &H8000 Then

 MsgBox "left arrow pressed"

    ElseIf GetAsyncKeyState(vbKeyPageUp) And &H8000 Then

       MsgBox "left arrow pageup pressed"

    ElseIf GetAsyncKeyState(vbKeyUp) And &H8000 Then

     MsgBox "up arrow pressed "

    ElseIf GetAsyncKeyState(vbKeyDown) And &H8000 Then
 MsgBox "down arrow pressed "

    ElseIf GetAsyncKeyState(vbKeyEscape) And &H8000 Then

      MsgBox "esc button pressed"

    ElseIf GetAsyncKeyState(vbKeyReturn) And &H8000 Then

    MsgBox "enter button pressed"

    ElseIf GetAsyncKeyState(vbKeyLButton) And &H8000 Then

    MsgBox "left mouse"
    ElseIf GetAsyncKeyState(vbKeyRButton) And &H8000 Then

    MsgBox "right mouse"
    ElseIf GetAsyncKeyState(vbKeyMButton) And &H8000 Then

    MsgBox "middle mouse"
    End If

End Sub

I found two references of things to TRY to catch the only button not working (for me and others - the LEFT MOUSE CLICK) but these two didn't work:

If CBool(GetAsyncKeyState(1) & 128) Then

    MsgBox ("left click trapped")
End If

Above started trapping for many keys yes left mouse but also ANY arrow key!

One other change to the original code above had a change to the left mouse click line to trap from:

    ElseIf GetAsyncKeyState(vbKeyLButton) And &H8000 Then

    MsgBox "left mouse"

changed it to this:

    ElseIf GetAsyncKeyState(vbKeyLButton) = 1 And &H8000 Then

    MsgBox "left mouse"

(added the =1 above but that also resulted in starting to catch MORE events (right click and/or arrows).

This is extremely frustrating since it is seemingly a super simple and popular request.  Please help!  If I could give more than 500 points I would!

Using Excel 2013, win 7 64 bit.
Question by:mizetreestump1
  • 3
  • 2
LVL 28

Accepted Solution

MacroShadow earned 2000 total points
ID: 39796154
Your misery has just come to an end! Use this:

Public Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Public Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer

Open in new window

Const VK_LEFT = &H25
Const VK_UP = &H26
Const VK_RIGHT = &H27
Const VK_DOWN = &H28
Const VK_TAB = &H9

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then    ' Change this Cell as requiered
        If GetKeyState(VK_UP) >= 0 And GetKeyState(VK_DOWN) >= 0 _
           And GetKeyState(VK_LEFT) >= 0 And GetKeyState(VK_RIGHT) >= 0 _
           And GetKeyState(VK_TAB) >= 0 And GetKeyState(VK_RETURN) >= 0 Then
            If Not GetAsyncKeyState(vbKeyRButton) And &H8000 Then
                MsgBox " You clicked on Cell : " & Target.Address, vbInformation
            End If
        End If
    End If
End Sub

Open in new window


Author Closing Comment

ID: 39796195
It appears there is no Tried and True means to detect a LEFT mouse click.  You have covered every possible way to select (hit) a cell OTHER than mouse click - arrows, tab, and return (Return hops you down one row).

One note - this traps when a cell is clicked via LEFT or RIGHT mouse button.  This is all I needed so I am happy to accept the solution.  I think I could use former example to tweak this if I needed to differentiate between left click and right click, but this suits my needs for this project!
LVL 28

Expert Comment

ID: 39796208
The code I posted only traps a left mouse click, that is insured by the following:
If Not GetAsyncKeyState(vbKeyRButton) And &H8000 Then

Open in new window

I Tested it myself!

Author Comment

ID: 39796237
Slight of hand?  I am looking at my sample spreadsheet and your initial solution only had one function - forGetKeyState, and no last IF for GetAsyncKeyState, but now it's there!  I tested and it works like a charm, thanks for this perfect solution! I've been looking everywhere.  Experts Exchange comes through again!
LVL 28

Expert Comment

ID: 39796242
My pleasure!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

916 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