Timestamp Log that does not overwrite previous timestamp

Question

I need to create a timestamp log of changes to a cell. For example if cell D1's value changes from previous value Column A will log the timestamp.

private sub worksheet_change(ByVal Target as Range)

if Range("d1") then
applicaton.enableevents = false
range("a1") = DATE + Time
applicaton.enableevents = true

end if
End sub

This is what I have so far, this logs the changes to D1 but it doesn't populate the next row of column A with a timestamp, it just modifies cell A1 with the current timestamp. I need to track the changes with a new entry of timestamp. I need it to populate the entire column A with timestamp every time there is a change to cell D1.

score 2 · Accepted Answer · edited May 23 '17 at 10:26

A solution:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$1" Then
      Application.EnableEvents = False
      Dim LastRow As Long, ws As Worksheet
      Set ws = Target.Worksheet
      LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      ws.Range(ws.Cells(LastRow + 1, 1), ws.Cells(LastRow + 1, 1)).Value = DateValue(Now()) & ", " & TimeValue(Now())
      Application.EnableEvents = True
    End If
End Sub

Notes:

It is important to fully qualify ranges.
Finding the last row might be tricky in some cases. See this. I assume you do not have one such case.

Matteo NNZ · Answer 2 · 2014-12-17T20:11:45.910

That's already a good start point. Now you need only to make sure the modification time is appended at the end of the last log registered in column A. I think replacing this line:

range("a1") = DATE + Time

with this line

Cells(Rows.Count, 1).End(xlUp).offset(1,0) = "D1 Modified on " & DateSerial(Year(Now()),Month(Now()),Day(Now())) & " at " & TimeSerial(Hour(Now()), Minute(Now()), Second(Now()))

should do the trick for what you want.

Timestamp Log that does not overwrite previous timestamp

2 Answers2