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

Question

When you have a data table in Excel, part of the standard functionality is that clicking a tab in the last cell adds a new row to the bottom of the table. I want to automatically populate this new line with useful defaults. In particular, I want to set the current date-time in one cell and copy the values ​​to some other cells from the previous row of the table.

This cannot be done using formulas - for example, using =now() for date-time stamping is inadequate, because it will be automatically updated every time the table is recalculated, whereas I want it to keep the date-date at the time the row is added.

So, I am trying to write VBA to trigger the event of the added row and in this code write the values ​​to the cells of the new row. From MS Documentation I thought DataTable.TableNewRow would be a suitable event. But when I try to write some code for this event, it fails. When I view a DataTable in the VBA Object Browser, the TableNewRow event TableNewRow not displayed.

Versions:

• VBA for 7.1 applications
• Excel 2013

So my questions are:

• Is my thinking direction right, or can you suggest a better approach?
• Can you suggest any working code that does something like this?
• Is DataTable.TableNewRow event that I have to work with?
• What do I need to do to get this event in my VBA code?

Answer 1

You can try the following:

Write this code in this book.

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

Then under the code in the 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 a public variable.

 Public ref_tbl As Range 

So basically, this will tell you when your table will increase in size.
If we can catch it, then you can do your things when this condition is met.
This works in the situation that you describe in your question.
This will not work if you insert a row between the records in the table. Anyway, hth.