Comment to track previous changes in cell

[Zimmerman]

Can anybody help me out with trying to track changes to each cell. I would love to be able to track what has changed in each cell with a comment. I have found this code below that does most of what I'm looking for except if it's possible I'd like to have the comment say what the previous value was? And i am wondering if it could keep a running track of each cell that changes rather than just the last change.


Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

windows XP
Excel 2003

 

[Weaver]

Else: preValue = preValue & vbcrlf & Target.Value

Not tested, but this might do what you're after.

Plus, you'd need to check in your first routine as to whether the cell had a comment already.

Are there likely to be comments not relating to cell changes in the sheet?

 

[Zimmerman]

No if i can get this comment tracking figured out then ther won't be any comments added in the sheet. I have it protected so the user can't add any if they tried. And I changed what you said it it tested out the same. It didn't say what the last value was.

Got anything else?

 

[Weaver]

try this:

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ts As String
    If Target.Count > 1 Then Exit Sub
    ts = ""
    On Error GoTo noComment
    ts = Target.Comment.Text & vbCrLf
noComment:
    On Error GoTo 0
    Target.ClearComments
    Target.AddComment.Text Text:=ts & "Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then
    preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

Admittedly, the final result might be a little inelegant, but you might be able to work on that.

 

[Zimmerman]

I think that will work. I just need to get what the previous value was in to this. Thank you very much for all your help.