How to catch new additions of lines in the list?

Question

I want to execute a VBA event handler in an Excel 10 worksheet whenever a user adds a new row to the list (the type of lists that Worksheet.ListObjects () returns instances) on this worksheet, for example, by entering data below the last row of the list (this extends the list by adding a new line to the list).

How can I do it? . Among other things, I want to set default values ​​for certain newline cells.

My current idea is to process Worksheet_Change and check if the Target parameter is in the .Range ListObject that interests me.

However, how would you know if a user creates a new row with a change in his cells and does this distinguish from changes to existing cells in the list?

I'm probably a little stupid here. I expect there will be a list event that I could capture, but I cannot find.

Answer 1

I think you're right, there are no events for ListObject . Using Worksheet_Change seems to be the right way. To detect New Row vs Existing Row changes, you will need to flip your own method.

I would suggest tracking the number of rows in ListOjects to detect when they change. To do this, try adding a hidden named range for each ListOject to store the current number of rows. Fill them in a file open and check them on Worksheet_Change .

This will add or update hidden name ranges in the open file (add to the workbook module)

 Private Sub Workbook_Open() Dim oList As ListObject Dim sh As Worksheet Dim nm As Name Dim strName As String For Each sh In Me.Worksheets For Each oList In sh.ListObjects 'oList.ListRows.Count strName = oList.Name & "Rows" Set nm = Nothing On Error Resume Next Set nm = Me.Names(strName) On Error GoTo 0 If nm Is Nothing Then Set nm = Me.Names.Add(strName, CStr(oList.ListRows.Count)) Else nm.RefersTo = CStr(oList.ListRows.Count) End If nm.Visible = False Next oList, sh End Sub 

This will determine which changes have been made. I made this a WorkBook-level event, so only one is required for all sheets. (add to workbook module)

 Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range) Dim oList As ListObject Dim nm As Name Dim strName As String For Each oList In sh.ListObjects strName = oList.Name & "Rows" If Not Application.Intersect(Target, oList.DataBodyRange) Is Nothing Then Set nm = Nothing On Error Resume Next Set nm = Me.Names(strName) On Error GoTo 0 If nm Is Nothing Then Set nm = Me.Names.Add(strName, CStr(oList.ListRows.Count)) nm.Visible = False End If If oList.ListRows.Count <> Val(Replace(nm.Value, "=", "")) Then nm.RefersTo = CStr(oList.ListRows.Count) MsgBox "List " & oList.Name & " changed" & vbCrLf & "New Line" Else MsgBox "List " & oList.Name & " changed" & vbCrLf & "Existing Line" End If End If Next End Sub 

Note. This does not apply when the name of an existing ListObject changes. This remains as an exercise for the reader.