Solved

Trap left mouse click in Excel when clicking on a cell

Posted on 2014-01-20
5
4,176 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
  • 3
  • 2
5 Comments
 
LVL 26

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 26

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 26

Expert Comment

by:MacroShadow
ID: 39796242
My pleasure!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

24 Experts available now in Live!

Get 1:1 Help Now