Add default values to new table row when using the Tab key?

Question

I'm currently working on a project in which the user needs to add new rows into a table and each column value has to have a definded default state.

This was quite simple using an ActiveX button that has the following code inside:

Private Sub cmdAddRowPiezas01_Click()
    Dim ThisSheetRef As Worksheet
    Set ThisSheetRef = ActiveWorkbook.Worksheets("Piezas")
    
    Dim myRow As ListRow
    Dim intRows As Integer
    
    Dim strNoData As String
    strNoData = "N/A"
    
    intRows = ThisSheetRef.ListObjects("tblPiezas01").ListRows.Count
    intRows = intRows + 1
    Set myRow = ThisSheetRef.ListObjects("tblPiezas01").ListRows.Add(intRows)

    myRow.Range(1) = intRows
    myRow.Range(2) = strNoData
    myRow.Range(3) = strNoData
    
End Sub

As you can see, what I'm doing is just adding a new row at the end of the "tblPiezas01" table and setting the values of each cell... but the user needs to click the button for this to happen.

Is there a way for me to make this code execute when they hit the "TAB" key at the end of the table instead?

It'd make the process of adding pre-formatted rows way easier (this table only has 3 columns, but I've got several others that include up to 10 columns some with different pre-established values.)

I've scoured the internet for a bit but I've run into the fact that Excel hasn't a built in "AddRow" event, I've seen some solutions that use the "Worsheet_Change" but so far I've got no luck making it work :/ (The code executes EVERY time the worksheet changes, therefore when I set the cell's value to something it triggers again... and that in turn triggers it again and so on).

Answer 1

To add a calculated column if your column already has manual data:

1: copy your existing data to a new range temporarily

2: highlight the entire column of each column that you want to have a default value. Here column a is =A7+1, column b & c are ="N/A"

3: tab to add a new row to your table first. Then paste your original data back into your table. The calculated formulas should work.

If you don't want the green error flags, you can turn them off in Options > Formulas > Inconsistent calculated column formula in tables

Answer 2

• If the bottom-right cell is non-blank after change, adding a new row is triggered.
• The change of other cells in table (ListObject) doesn't trigger it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LO As ListObject
    Const TAB_NAME As String = "tblPiezas01"
    If Target.CountLarge = 1 And Len(Target.Cells(1).Value) > 0 Then
        Set LO = Me.ListObjects(TAB_NAME)
        If Not Application.Intersect(LO.Range.Columns(LO.ListColumns.Count), Target) Is Nothing Then
            If Target.Offset(1).ListObject Is Nothing Then
                Dim myRow As ListRow
                Dim strNoData As String
                strNoData = "N/A"
                Set myRow = LO.ListRows.Add
                Application.EnableEvents = False
                myRow.Range(1) = LO.ListRows.Count
                myRow.Range(2) = strNoData
                myRow.Range(3) = strNoData
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

---

• Remove the last row if users double click any cell on the last row

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim LO As ListObject
    Const TAB_NAME As String = "tblPiezas01"
    If Target.ListObject.Name = TAB_NAME Then
        Set LO = Target.ListObject
        If Target.Offset(1).ListObject Is Nothing Then
            Application.EnableEvents = False
            LO.ListRows(LO.ListRows.Count).Delete
            Cancel = True
            Application.EnableEvents = True
        End If
    End If
End Sub

Answer 3

A Worksheet Change: Add New Row to Excel Table (ListObject)

• Out-comment or remove the Debug.Print lines after investigating.
• The thing I don't like is that it adds a new line after pressing TAB even when there is already a new line. Shouldn't be an issue. If it is, it would require some code to delete it.
• What I added is that if the last cell is empty it will not add a new row if TAB is not pressed.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const NO_DATA_STRING As String = "N/A"
    Const TABLE_NAME As String = "tblPiezas01"
    
    Dim lo As ListObject: Set lo = Target.Worksheet.ListObjects(TABLE_NAME)
    
    Dim tcell As Range:
    With lo.DataBodyRange
        Set tcell = .Cells(.Cells.CountLarge)
    End With
    
    Dim irg As Range: Set irg = Intersect(tcell, Target)
    If irg Is Nothing Then Exit Sub
    
    Dim rrg As Range, rCount As Long, cCount As Long
    
    With lo.DataBodyRange
        rCount = .Rows.Count
        cCount = .Columns.Count
        Set rrg = .Rows(rCount)
    End With
Debug.Print rrg.Address, rCount, cCount
    
    Dim WasNewRowAdded As Boolean, IsRightCellEmpty As Boolean
    
    If Application.CountBlank(rrg) = cCount Then
        WasNewRowAdded = True
    Else
        If IsEmpty(rrg.Cells(cCount)) Then
            IsRightCellEmpty = True
        End If
    End If
    
Debug.Print IsRightCellEmpty, WasNewRowAdded
    
    If Not IsRightCellEmpty Then
        If Not WasNewRowAdded Then
            lo.ListRows.Add
            Set rrg = lo.ListRows(lo.ListRows.Count).Range
            rCount = rCount + 1
        End If
Debug.Print rrg.Address, rCount, cCount
        Application.EnableEvents = False
            rrg.Cells(1) = rCount
            rrg.Resize(, cCount - 1).Offset(, 1).Value = NO_DATA_STRING
        Application.EnableEvents = True
    End If

End Sub