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

Question

In short, I would like to run sub every time I click in a cell

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

I write VBA code inside an area because I would like this south to run in every sheet that it has. ThisWorkbook

My goal: when I click on a cell (it does not matter in the Sheet), the routines start, perform some checks and then write the result to a specific cell (in the same active sheet).

Is it possible to handle this event?

Thank.

Answer 1

You must use the SheetChangeobject method Workbook:

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

, : ( ), , ( ).

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

. , , . , " " " " ( , , , : VBA [ Range("A1").Select], /// ). , , , .

( ?), API user32.dll :

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

. PtrSafe API 64- . 32-, API.

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

. , , , . , , 95% , , .

, 100% SelectionChange ( ), , ( ):

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

- , SelectionChange ( ).