Default Value for new Table Row

  • Thread starter Thread starter John W. Kneitz
  • Start date Start date
J

John W. Kneitz

Is there a way to specify a "default" value for a field when a new row is
created in a table?

I have a simple table with the date as the first column. When I add a new
entry to the table I would like the date to default to the current day. I
tried browsing the Excel objects but could not find any objects or events
related to tables. I could develop a form, but that would be a lot of work
just to set a simple default. It seems seems like there should be an
"on_new_table_row" or "on_table_row_add" event where you could set a default
with just a line or two of code.

Thanks,

John W. Kneitz
(e-mail address removed)
 
You can do this with VBA
Right-click the sheet tab
Click 'View code
Past the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect
If Target.Count = 1 Then
Set isect = Application.Intersect(Target, Range("B:B"))
If Not isect Is Nothing Then
Target.Offset(, -1) = Date
End If
End If

End Sub

When a value is entered in column B, today's date will appear next to it in
column A.

NOTE : This will also happen if somebody amends an earlier entry in column
B. If this is undesired, the following checks that the cell immediately
below the target cell is empty.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect
If Target.Count = 1 Then
If Target.Offset(1) & "" = "" Then
Set isect = Application.Intersect(Target, Range("B:B"))
If Not isect Is Nothing Then
Target.Offset(, -1) = Date
End If
End If
End If

End Sub
 
Thanks, that works but affects rows outside the table range. Here is what I
wound up using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Table As Range

Set Table = Target.Worksheet.ListObjects(1).Range
If Target.Row <> (Table.Row + Table.Rows.Count - 1) Then
End
End If
If Target.Worksheet.Cells(Target.Row, 1) = "" Then
Target.Worksheet.Cells(Target.Row, 1) = Date
End If

End Sub

It is very similar to your suggestion. For a "production" environment it
would need some additional sanity chekcs tests but since it's only me I can
live with the limitations.

I really need to brush up on my VBA. It's been 10+ years since I used it!

Thanks again.

John
 
Back
Top