数据表的新行中的字段的默认值

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.

当您在Excel中有数据表时，标准功能的一部分是在最后一个单元格中按Tab键会在表格的底部添加一个新行。我想使用有用的默认值自动填充该新行。特别是我想将当前日期时间放在一个单元格中，并将值复制到表格前一行的其他一些单元格中。

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.

使用公式来做这件事是行不通的 - 例如using = now（）用于日期时间戳是不合适的，因为每次电子表格重新计算时它都会自动更新，而我希望它在添加行时保留日期时间。

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.

因此，我试图将VBA编写为由添加的行的事件触发，并在该代码中将值写入新行的单元格中。从MS文档我认为DataTable.TableNewRow将是适当的事件。但是当我尝试为该事件编写任何代码时，它都没有被执行。当我在VBA对象浏览器中查找DataTable时，未列出TableNewRow事件。

Versions:

版本：

VBA for Applications 7.1
VBA for Applications 7.1
Excel 2013
Excel 2013

So my questions:

所以我的问题：

Is the direction of my thinking right, or can you suggest a better approach?
我的思维方向是正确的，还是你能提出更好的方法？
Can you offer any working code that does something like this?
你能提供任何类似的工作代码吗？
Is DataTable.TableNewRow the event I should be working with?
DataTable.TableNewRow是我应该使用的事件吗？
What do I need to do to get that event accessible in my VBA code?
要在我的VBA代码中访问该事件，我需要做什么？

1 个解决方案

#1

You can try this:

你可以试试这个：

Write this code in Thisworkbook.

在Thisworkbook中写下这段代码。

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

Then below code in a Worsksheet Object.

然后在Worsksheet对象中的代码下面。

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

您还需要一个Module来声明公共变量

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.

所以基本上，这将告诉你表的大小增加。如果我们能够捕获它，那么你可以在满足条件时完成你的工作。这适用于您在问题中描述的情况。但是，当您在表中的条目之间插入行时，它将无法工作。无论如何，HTH。

#1