How to run code when clicking a cell?

Question

I'm aware that I can use Worksheet_SelectionChange but this doesn't do exactly what I want.

For example when I move the active cell with the arrow keys it will still run the code.

How do I only make it run the code when actually physically clicking on the cell?

Answer 1

You can detect left clicks as well. I answered a similar question here

Insert the following code inside the specific worksheet module (for example "Sheet1"):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (GetAsyncKeyState(vbKeyLButton)) Then 'left mouse button
            'do something here
    End If
End Sub

additionally, you have to insert the following part on the top of a normal module (like the standard "Module1"):

Public Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

That's it. The part "do something here" can be filled by your needs.

However, this has some flaws: 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. To bypass this, you can add an extra "empty"

If (GetAsyncKeyState(vbKeyLButton)) Then 'left mouse button 'blank End If

in the end of the selectionChange Sub. As I said, there are flaws, so this won't disable all unwanted behaviour by Excel. Another one is clicking somewhere else in Excel (e.g. choosing another ribbon) and changing the selection of cells per arrow keys afterwards. Haven't found a solution to that one unfortunately.

Answer 2

The left mouse click cannot be trapped with VBA for the Excel application. There are some methods to check globally if the left mouse button is pressed down but it does not seem simple and reliable from what I gather. See:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28343702.html which is unresolved (basically the same question you have)

SelectionChange cannot only work for the mouse: http://excel.tips.net/T003070_Mouse_Click_Event_in_VBA.html

After lots of searching nothing on the web is conculsive about this. This may not be the answer you're looking for but I don't think you'll find what you want.