Solved

Trap left mouse click in Excel when clicking on a cell

Posted on 2014-01-20
5
4,992 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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