Default values for fields in a new row of a data table

Question

When you have a data table in Excel, part of the standard functionality is that pressing tab in the last cell adds a new row at the bottom of the table. I want to auto-populate that new row with useful default values. In particular I want to put current date-time in one cell, and copy values into some other cells from the previous row of the table.

It is not workable to do that using formulae -- e.g. using =now() for the date-time stamp is inadequate because it will be auto-updated every time the spreadsheet recalculates, whereas I want it to retain the date-time at the moment when the row was added.

So I am trying to write VBA to be triggered by the event of the row being added, and in that code to write values into the cells of the new row. From MS documentation I thought DataTable.TableNewRow would be the appropriate event. But when I try to write any code for that event it is not being executed. When I look up DataTable in the VBA object browser the TableNewRow event is not listed.

Versions:

• VBA for Applications 7.1
• Excel 2013

So my questions:

1. Is the direction of my thinking right, or can you suggest a better approach?
2. Can you offer any working code that does something like this?
3. Is DataTable.TableNewRow the event I should be working with?
4. What do I need to do to get that event accessible in my VBA code?

Answer 1

You can try this:

Write this code in Thisworkbook.

Private Sub Workbook_Open()
    Set ref_tbl = Sheet1.ListObjects(1).DataBodyRange
End Sub

Then below code in a Worsksheet Object.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo halt
    Application.EnableEvents = False
    Dim tbl_rng As Range
    Set tbl_rng = Me.ListObjects(1).DataBodyRange
    If Not Intersect(Target, tbl_rng) Is Nothing Then
        If tbl_rng.Rows.Count > ref_tbl.Rows.Count Then
            MsgBox "Table increase in size"
            '~~> Do your stuff here
            Set ref_tbl = tbl_rng
        End If
    End If
forward:
    Application.EnableEvents = True
    Exit Sub
halt:
    MsgBox Err.Number & ": " & Err.Description
    Resume forward
End Sub

You will also need a Module to declare the public variable

Public ref_tbl As Range

So basically, this will tell you when your table increase in size.
If we're able to capture that, then you can do your stuff when that condition is met.
This works in the situation you describe in your question.
It will not work though when you insert row between entries in the table. Anyways, HTH.