Capturing the auto-insertion of a row to a table via a Tab in VBA

[gravanoc]

I have written code to capture adding a row using the command or when typing new data in a blank cell in a row beneath the last table row, however, when someone tabs at the end of the last row of data and in the last cell, it will automatically insert a new row in the table. This doesn't trigger any normal events that I have setup. Anyone know how to tell when this has happened?

 

[Anthony47]

Probably you can use the WorksheetChange event: when a new row is added to the table Target.Address will refer to the last row of the table; also, when you tab at the end on the table, the selection will move to cell 1 of the table row.
So probably this code will catch the situation:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ck1 As Boolean, Ck2 As Boolean
'
On Error Resume Next
Ck1 = (Selection.ListObject.ListRows(Selection.ListObject.DataBodyRange.Rows.Count).Range.Cells(1, 1).Address = Selection.Address)
Ck2 = (Selection.ListObject.ListRows(Selection.ListObject.DataBodyRange.Rows.Count).Range.Address = Target.Address)
On Error GoTo 0
If Ck1 And Ck2 Then
    'what to do When new line is addedd
    'what to do When new line is addedd
    MsgBox ("NewLine manually Inserted")
End If
End Sub

The code has to be inserted into the vba code sheet of the working sheet:
-rightclick on the tab with the name of the sheet and chose View Code to open the vba editor at the right page
-copy the code and paste it into the right frame of the page

Return to Excel and test for the various conditions

Bye

 

[gravanoc]

Probably you can use the WorksheetChange event: when a new row is added to the table Target.Address will refer to the last row of the table; also, when you tab at the end on the table, the selection will move to cell 1 of the table row.
So probably this code will catch the situation:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ck1 As Boolean, Ck2 As Boolean
'
On Error Resume Next
Ck1 = (Selection.ListObject.ListRows(Selection.ListObject.DataBodyRange.Rows.Count).Range.Cells(1, 1).Address = Selection.Address)
Ck2 = (Selection.ListObject.ListRows(Selection.ListObject.DataBodyRange.Rows.Count).Range.Address = Target.Address)
On Error GoTo 0
If Ck1 And Ck2 Then
    'what to do When new line is addedd
    'what to do When new line is addedd
    MsgBox ("NewLine manually Inserted")
End If
End Sub

The code has to be inserted into the vba code sheet of the working sheet:
-rightclick on the tab with the name of the sheet and chose View Code to open the vba editor at the right page
-copy the code and paste it into the right frame of the page

Return to Excel and test for the various conditions

Bye

I was being stupid and didn't realize I had events off when I was trying earlier, and I soon realized that it was captured normally by Selection change. Thanks!