How to run a sub every time I change the cell selection - excel, vba

Question

Long story short is: I'd like to run a sub everytime I click in a cell

I'm stuck here with this sub in excel and I need your help.

I'm writing a VBA code inside ThisWorkbook area because I'd like this sub to run in every Sheet it has.

My goal is: When I click in a cell (doesn´t matter the Sheet), the sub runs, make some checks and then write in a specific cell (in the same active Sheet) the result.

Is it possible to handle this event?

Thank you.

Answer 1

You have to use the SheetChange method of the Workbook object:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'run code everytime one of the sheets is changing
End Sub

Sorry, I've just read better this: When I click in a cell (doesn´t matter the Sheet), the sub runs, make some checks and then write in a specific cell (in the same active Sheet) the result.

In that case, you should rather use the Workbook_SheetSelectionChange:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'run code everytime one of the sheets' cells is selected
End Sub

However, be careful. The above would run when the selection changes, not when you click on a cell. Let's say that "clicking on a cell" is a sub-set of "selecting a cell" (because if you're clicking on a cell you're selecting a cell, but it's not necessarily true that if you're selecting a cell you're clicking on it: you might be selecting it either via VBA code [Range("A1").Select] or by moving up/down/right/left with the mouse). So don't forget to manage the difference if you don't want your code to run everytime the selection is changed.

To forecast the next question (how do I distinguish between click or not click?), you may use the user32.dll API to get the click:

Private Declare PtrSafe Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey As Long) As Integer

Note: the PtrSafe keyword is used to declare the API in 64-bit systems. If you've a 32-bit one, just take it out from the API declaration.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim check As Boolean
    check = GetAsyncKeyState(1) '<-- True if click, False if not click
    If check Then '<-- if click...
        'run code everytime one of the sheets' cells is selected and you intercept a click
    End If
End Sub

To conclude

I quote Siddharth's comment. I've answered this question to show you how to do what you ask. If you say what is exactly that you want to do, there will be 95% sure a much better solution than the one I've written above.

And if you want to see why you should never 100% trust the SelectionChange (credits to Siddharth for the tip), hide a row in your sheet and then run this macro (put it into a standard module):

Sub test()
    Dim r As Range
    r = Cells.SpecialCells(xlCellTypeVisible)
End Sub

Even if you're not selecting any cell, the SelectionChange event will be fired anyway (so risking a wrong data validation).