Excel VBA: Determine mouse location when clicking on a cell

Question

What I tried to do was write an Excel SelectionChange handler such that when the user selects a single cell with the mouse I can (a) know that it was the mouse and not the keyboard and (b) find out where in the cell the mouse click occurred. (I would exclude situations in which the user selected more than one cell).

Suppose there were several lines of text in a cell. The idea is to do something different depending on which part of the cell was clicked. (And it doesn't work for my purposes to have multiple cells.)

[Update: I decided that (b) was hopeless and dropped that. But I still wanted to know when a selection changed because of a left mouse click.]

Thanks for any ideas.

---

EDIT: I found the following resources that led me to a solution that seems to work. I'm also including code for my solution.

Sources:

EngJon's answer

Both answers here

This more general explanation

VBA keycode constants

EngJon noted a problem with their solution because it picked up mouse clicks originating elsewhere. For example, after clicking on the ribbon, using an arrow key to change the selection will still show the left-click in the selection change handler.

I played with this a while and decided (though I could be wrong) that GetAsyncKeyState()returns True or False for the most recent mouse click and the most recent keypress. The way it handles combination key presses like SHIFT-TAB is to return True for both the shift and tab keys. [Treating 0 as False and all nonzero values as True.]

(You'll note that the keycode constants have no value for combinations like back tab, which is SHIFT-TAB. Similarly, I'll bet, though I didn't test it, that you find combinations like SHIFT-RIGHTCLICK by testing GetAsyncKeyState(vbKeyShift) AND GetAsyncKeyState(vbKeyRButton).)

So, in a module (e.g., Module1), I put ...

Option Explicit

#If VBA7 Then
    'declare virtual key event listener
    Public Declare PtrSafe Function GetAsyncKeyState Lib "user32" _
            (ByVal vKey As Long) As Integer
#Else
    'declare virtual key event listener
    Public Declare Function GetAsyncKeyState Lib "user32" _
            (ByVal vKey As Long) As Integer
#End If

In the code for the sheet where I wanted to trap keystrokes and mouse clicks, I put ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    'Check whether the left mouse key was recently pressed.
    
    Select Case True

     Case GetAsyncKeyState(vbKeyUp) Or GetAsyncKeyState(vbKeyRight) _
            Or GetAsyncKeyState(vbKeyDown) Or GetAsyncKeyState(vbKeyLeft) _
            Or GetAsyncKeyState(vbKeyTab) Or GetAsyncKeyState(vbKeyLeft) _
            Or GetAsyncKeyState(vbKeyHome) Or GetAsyncKeyState(vbKeyReturn)

        'One of the cell selection keys was pressed, so do nothing.
        ' For some reason, PageUp, PageDown, and End don't seem
        ' to be needed here.
     
     Case GetAsyncKeyState(vbKeyLButton)

        'Left mouse key was pressed
        
        <your code here>
     
    End Select

End Sub

You might ask why the first Case statement is there. As EngJon stated, "If you do something that finishes with a click (e.g. a MsgBox), the next selection change with arrow keys will also fire the event and re-execute your stuff." I found that checking for the keystrokes before checking for the mouse click gets rid of this problem.

For me, so far, this has worked flawlessly to pick up solo left-clicks on a cell that I detect in the <your code here> section.

Answer 1

[Also included in my edit to the original question above.]

I found the following resources that led me to a solution that seems to work. I'm also including code for my solution.

Sources:

EngJon's answer

Both answers here

This more general explanation

VBA keycode constants

EngJon noted a problem with their solution because it picked up mouse clicks originating elsewhere. For example, after clicking on the ribbon, using an arrow key to change the selection will still show the left-click in the selection change handler.

I played with this a while and decided (though I could be wrong) that GetAsyncKeyState()returns True or False for the most recent mouse click and the most recent keypress. The way it handles combination key presses like SHIFT-TAB is to return True for both the shift and tab keys. [Treating 0 as False and all nonzero values as True.]

(You'll note that the keycode constants have no value for combinations like back tab, which is SHIFT-TAB. Similarly, I'll bet, though I didn't test it, that you find combinations like SHIFT-RIGHTCLICK by testing GetAsyncKeyState(vbKeyShift) AND GetAsyncKeyState(vbKeyRButton).)

So, in a module (e.g., Module1), I put ...

Option Explicit

#If VBA7 Then
    'declare virtual key event listener
    Public Declare PtrSafe Function GetAsyncKeyState Lib "user32" _
            (ByVal vKey As Long) As Integer
#Else
    'declare virtual key event listener
    Public Declare Function GetAsyncKeyState Lib "user32" _
            (ByVal vKey As Long) As Integer
#End If

In the code for the sheet where I wanted to trap keystrokes and mouse clicks, I put ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    'Check whether the left mouse key was recently pressed.
    
    Select Case True

     Case GetAsyncKeyState(vbKeyUp) Or GetAsyncKeyState(vbKeyRight) _
            Or GetAsyncKeyState(vbKeyDown) Or GetAsyncKeyState(vbKeyLeft) _
            Or GetAsyncKeyState(vbKeyTab) Or GetAsyncKeyState(vbKeyLeft) _
            Or GetAsyncKeyState(vbKeyHome) Or GetAsyncKeyState(vbKeyReturn)

        'One of the cell selection keys was pressed, so do nothing.
        ' For some reason, PageUp, PageDown, and End don't seem
        ' to be needed here.
     
     Case GetAsyncKeyState(vbKeyLButton)

        'Left mouse key was pressed
        
        <your code here>
     
    End Select

End Sub

You might ask why the first Case statement is there. As EngJon stated, "If you do something that finishes with a click (e.g. a MsgBox), the next selection change with arrow keys will also fire the event and re-execute your stuff." I found that checking for the keystrokes before checking for the mouse click gets rid of this problem.

For me, so far, this has worked flawlessly to pick up solo left-clicks on a cell that I detect in the <your code here> section.

---

UPDATE: See my new question here. There seems to be a bug somewhere (Excel or Windows) such that GetAsyncKeyState(vbLKeyButton) always returns 0 after pasting into a worksheet cell. Fortunately, it seems to work correctly for key presses, so if you cover all the possible keys involved in changing the selection, and those are all 0, then you really don't have to confirm that it was changed via the mouse.

---

ADDITIONAL NOTE: Having read everyone's favorite MSDN documentation on the matter, I wondered what the practical difference was between using GetKeyState() and GetAsyncKeyState(). After extensive testing, I decided that as far as the Excel message queue is concerned, looking just at the values returned in the Worksheet_SelectionChange event, GetKeyState() seems to give an interesting historical record of key presses. In integer values, for a given key you get:

• -127 = this was the last key pressed and the previous value for this key was 0
• -128 = this was the last key pressed and the previous value for this key was 1
• 0 = some other key or mouse event occurred and this key's most recent pressed value was -128
• 1 = some other key or mouse event occurred and this key's most recent pressed value was -127

The 0 or 1 values persist until that key is pressed again.

If you click somewhere else--say into a different worksheet--and press some navigation keys, then click back to the worksheet with the event handler, I'm not sure, but I think the state of the keys will depend on the intervening key presses. On the other hand, if all you're interested in is the key press that triggered the event, you probably can just check for a negative key value.

The bothersome thing is that GetKeyState() does not reliably tell you when the mouse was just clicked. Left clicks result in alternating values of 0 and 1. If you click elsewhere, like in the ribbon or on the workbook window edges, each click results in a toggle of the value, so when you finally click in the worksheet and trigger the Worksheet_SelectionChange event, you don't know whether you're going to get a 0 or 1.

My observations of GetKeyState() results for a series of actions:

Answer 2

Despite all the detailed notes in my original question and answer, I found that there is no practical way to determine whether a cell/range was selected using the mouse rather than keystrokes or, perhaps more importantly, by the Find operation.

It is not possible to use GetAsyncKeyState() to know when the selection change was due to the mouse because:

1. If you click somewhere like the ribbon or window border, then change cells using the keyboard, the function shows the mouse was clicked, which is correct but not useful.
2. If you first check whether a navigation key was pressed, the logic fails if the cell was entered via the Find dialog--that is, the user clicked on "Find Next," so the function shows that no keys were pressed and that the mouse was, which is correct but not useful.
3. If those could be overcome, there is a bug that causes the function to return 0 for the left mouse button every time after a copy/paste in the worksheet (see my related question here).

It is not possible to use GetKeyState() to know when the selection change was due to the mouse because for the left mouse button, the function always alternates between the values 0 and 1 (see the illustration in my earlier answer).

My workaround, though I don't especially like it, was to require the user to double-click in order to trigger my code. The Excel before-double-click event is the only one that captures left mouse clicks. This actually simplified the logic but was less desirable for my intended user interaction.

What I really needed from Excel was a before-left-click event.