Comment to track changes?

[alex8447]

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?

 

[alex8447]

anyone?

 

[gaj104]

Give this a whirl

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ccc As Range
Dim comment As String

If Not Intersect(Target, Range("B4:BC711")) Is Nothing Then

    comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
    Target.Cells.NoteText comment
    
End If

End Sub

 

[MickG]

Try :-

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

 

[alex8447]

Hey i really appreciate you guys helping me out, Mickg i liked what your code did, but is there a way to keep a log of each change in the comment.

For example say i edit the cell and it then says

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

in the comment and then I edit it again 5 minutes later, can you make it so that it will keep the previous comment and just add the new comment below it within the same comment so it's a log? So when I edit it again it would look like this in the comment:

"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"

 

[MickG]

Try this:-

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
   If Target.comment Is Nothing Then
        Target.Cells.NoteText ("Cell Last Edited: ") & Now & (" by ") & Application.UserName & Chr(10) & "Text :" & Target.Value
   Else
        Target.Cells.NoteText oval & Chr(10) & ("Cell Last Edited: ") & Now & (" by ") & Application.UserName & Chr(10) & "Text :- " & Target.Value
    End If
End If
Target.comment.Shape.TextFrame.AutoSize = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target.comment Is Nothing Then
    oval = Target.comment.Text
End If
End Sub

 

[alex8447]

Guys I appreciate your help this is the final working code I am using!

Option Compare Text
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

   Const sRng As String = "B4:BC711"   ' change as required
   
   Dim sOld As String
   Dim sNew As String
   Dim sCmt As String
   Dim iLen As Long
   Dim bHasComment As Boolean

   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 = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & Application.UserName & Chr(10) & "Previous Text :- " & sOld

      If Target(1).Comment Is Nothing Then
         .AddComment
      Else
         iLen = Len(.Comment.Shape.TextFrame.Characters.Text)
      End If

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