Track Cell History in Comment?

Hey folks!

I am new to the site, have been a lurker for some time now grabbing code left and right when I need it but I have run into an issue I do not know the code for.

What I need to do is track each change within each cell from B4 to BC711 with a comment on the cell that shows what was previously in the cell before the change, as well as track the User, Date and Time of the Last Edit of the cell, (need all of this in the comment of each cell). (All for Excel 2007 in Windows XP)

I currently have the code to track the User, Time, and Date of each edit of each cell with a comment, but it is for the entire spreadsheet.


Here is the code I am currently using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ccc As Range
Dim comment As String
comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.Cells.NoteText comment
End Sub

So I was wondering if anyone knows the code for this?

Perhaps you can adapt this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const sRng      As String = "A1:A10"    ' change as required
    Dim sOld        As String
    Dim sNew        As String
    Dim sCmt        As String
    Dim iLen        As Long

    With Target(1)
        If Intersect(.Cells, Range(sRng)) Is Nothing Then Exit Sub
        sNew = .Text
        Application.EnableEvents = False
        Application.Undo
        sOld = .Text
        .Value = sNew
        Application.EnableEvents = True

        sCmt = Format(Date, "yyyy-mmdd") & ": Was """ & sOld & """ is """ & sNew & """"

        If .Comment Is Nothing Then .AddComment

        With .Comment.Shape.TextFrame
            iLen = Len(.Characters.Text)
            .AutoSize = True
            .Characters(Start:=iLen + 1).Insert IIf(iLen, vbLf, "") & sCmt
        End With
    End With
End Sub

K i have some more details for you so its clearer:

I finally got the code almost where I need it, right now it's within the parameters I need but the only thing left I need it to do is keep a log of each change of each cell in the comment.

For example right now this is what the comment looks like:

"Cell Last Edited: 7/29/2011 9:48:24 AM by alex8447
Previous Text :- Verified & Correct"

but when I edit it again, it only replaces the previous comment with the new edit. I need it to keep a log in the comment so when I make a 2nd edit it will keep the first edit information in the comment as well. The comment would look like this if I edited the cell twice:

"Cell Last Edited: 7/29/2011 9:48:24 AM by alex8447
Previous Text :- Verified & Correct

Cell Last Edited: 7/29/2011 9:52:31 AM by alex8447
Previous Text :- Incorrect"


Here is the code i am currently using: would you know how to modify it to do log edits like i was saying??

Option Explicit
Dim oVal As String
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ccc As Range
Dim comment As String
If Not Intersect(Range("B4:BC711"), Target) Is Nothing Then
    comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName & Chr(10) & "Previous Text :- " & oVal
    Target.Cells.NoteText comment
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oVal = Target
End Sub

Alex, please pick a forum; either continue here or at MrExcel.

Please take a few minutes to read the forum rules about cross-posting.

You can add Ozgrid to the list of cross posts