I have a VBA trigger listening for user changes in the sheet. It is also a sheet that Queries a SQL Database and prints the information. When the user clicks "Refresh All", it also triggers the "Worksheet_Change" Sub.
How can I prevent the Worksheet_Change sub from activating on "Refresh All", or check how recently the Query was run (to skip the logic in the sub after it's called).
Private Sub Worksheet_Change(ByVal Target As Range)
' Jump to end to return functionality if an error occurs
On Error GoTo haveError
' Prevent infinite loop of self updates
Application.EnableEvents = False
Dim change_range, change_cell As Range
Dim notActive As Boolean
Dim cell_count As Integer
Dim changed_cell_array As Variant
Set change_range = Application.Intersect(Target, Me.Range("A:G"))
cell_count = 0
ReDim changed_cell_array(3, 0)
If change_range Is Nothing Then
' Cell update was not in range
Else
' Loop through all cells in update trigger
For Each change_cell In change_range.Cells
If Not IsEmpty(change_cell) Then ' Ignore the empty cells
' Debug.Print "Target ID = " & CStr(Cells(change_cell.Row, 1).Value) & vbTab & vbTab & "Cell Value = " & CStr(change_cell.Value)
cell_count = cell_count + 1
ReDim Preserve changed_cell_array(3, cell_count)
changed_cell_array(0, cell_count) = Cells(change_cell.Row, 1).Value
changed_cell_array(1, cell_count) = change_cell.Value
changed_cell_array(2, cell_count) = change_cell.Row
changed_cell_array(3, cell_count) = change_cell.Column
End If
Next change_cell
End If
Debug.Print CStr(cell_count) & " cells changed"
RunPython "import testing2; testing2.pass_array(" & CStr(cell_count) & ")"
haveError:
' Return excel to its normal state
Application.EnableEvents = True
End Sub
Maybe check to see if Target is a specific range corresponding to the query table or whatever is getting updated on that sheet?
Something you can use to exit the event handler without taking further action.
For example a test like this might exit the event handler if a entire Table/ListObject is updated:
If Target.Address = Me.ListObjects("TableResult").Range.Address Then Exit Sub