As if that weren't easy enough, here is a macro that write the date without pressing any keys AND move the cursor to the next cell. Install this code in a normal code module (by default 'Module1'):
- Code: Select all
Public Sub CreationDate(ByRef Target As Range)
Const CreateColumn As String = "B"
With Target
With .Worksheet.Cells(.Row, CreateColumn)
If Not IsDate(.Value) Then .Value = Date
.Offset(0, 1).Select
End With
End With
End Sub
The above code is triggered by a Change Event in the worksheet where you want the date written. Paste the following code in the code sheet of the worksheet in which you wish to write the date (by default 'Sheet1'):
- Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Const TriggerColumn As String = "A"
Const HeaderRows As Long = 1
With Target
If .Column = Asc(TriggerColumn) - 64 Then
If .Row > HeaderRows And _
Trim(.Value) <> vbNullString Then _
CreationDate Target
End If
End With
End Sub
It makes sense to let the date be added whenever you start writing in a row, i.e. whenever you create a new row at the bottom of your worksheet, but if you want the TriggerColumn to be a column other than column A just change the constant's TriggerColumn value to any value from B to Z. The constant HeaderRows indicates the number of rows at the top of your sheet where you don't want any date written, ever. You can set any value from zero up, but not below.
This code will call the date-writing procedure if the change is made anywhere in the TriggerColumn except in the header rows and provided that the changed cell isn't blank after the change.