Trap left mouse click in Excel when clicking on a cell

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.
Who is Participating?
MacroShadowConnect With a Mentor Commented:
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

mizetreestump1ConsultantAuthor Commented:
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!
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!
mizetreestump1ConsultantAuthor Commented:
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!
My pleasure!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.