CODEWITHSUNDEEP
excelvbatimestamp
Yonip
Yonip

Reputation: 11

Timestamp Log that does not overwrite previous timestamp

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.

Upvotes: 0

Views: 323

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

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:

  1. It is important to fully qualify ranges.

  2. Finding the last row might be tricky in some cases. See this. I assume you do not have one such case.

Upvotes: 2

Matteo NNZ
Matteo NNZ

Reputation: 12665

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.

Upvotes: 1

Related Questions