Saturday, November 19, 2011

Excel: quick auto-timestamps

If you want to add a quick & simple auto-timestamp whenever a cell is affected, here's some sample code:


Private Sub Worksheet_Change(ByVal Target As Range)
'add the below line if you want to avoid timestamping null cells
'If Target.Value = "" Then Exit Sub
On Error GoTo ws_exit:
' set the date
Application.EnableEvents = False
With Cells(Target.Row, 15)
.NumberFormat = "dd/mm/yyyy h:MM:ss AM/PM"
.Value = Now()
.Columns.AutoFit
End With
ws_exit:
    Application.EnableEvents = True
End Sub
Note that this will catch any Change event and then put the timestamp into column 15 (O).
This allows for a quick audit feature whenever checking an issues log to see which items were updated recently.

-ryan

No comments:

Post a Comment