Call Function on Table Row Insert

[ascalise]

I have a table that I update with a SQL query called manually via command button. Looking to automate it to trigger immediately when a new row is added. I currently have code to reformat the sheet based on if any cells in the table are changed, but that would cause the function to be called excessively. Any ideas? I thought about defining a static variable for table length on workbook open and comparing whenever the table is changed, but I'm not entirely sure how to do it. Here's what I currently have:

Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject: Set tbl = ListObjects(1)
If Not Intersect(tbl.Range, Target) Is Nothing Then
    'SQL query
End If

 

[ascalise]

Figured it out courtesy of this thread I found from 2014. Using 2 helper cells I can determine if the length of the table has changed.

Tweaked it a bit, but this is what I've ended up with. Will only run on workbook close instead of sheet calculate, and will only trigger if rows are added, not removed. Hope this may be helpful to anyone else looking to accomplish the same.

Helper function in module to calulcate last row:

 Public Function LastRow() As Long
    Application.Volatile
    ActiveSheet.AutoFilterMode = False
    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

Workbook code to execute on save (if table length has changed):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Worksheets("Sheet1").Range("H1").Value > Worksheets("Sheet1").Range("I1").Value Then
        MsgBox "Table length increased"
        Worksheets("Sheet1").Range("I1").Value = Worksheets("Sheet1").Range("H1").Value
    Else
        Worksheets("Sheet1").Range("I1").Value = Worksheets("Sheet1").Range("H1").Value
    End If
End Sub