Solved

Trap left mouse click in Excel when clicking on a cell

Posted on 2014-01-20
5
5,270 Views
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.
1
Comment
Question by:mizetreestump1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 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_RETURN = &HD
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

0
 

Author Closing Comment

by:mizetreestump1
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!
0
 
LVL 27

Expert Comment

by:MacroShadow
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!
0
 

Author Comment

by:mizetreestump1
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!
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39796242
My pleasure!
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

635 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