Track Changes in Comment

[karrims]

Hi Guys,
I've been reading though various threads all day and can't quite seem to find one that does what I need: I want to track the changes to cells in a spreadsheet using the Add Comment. I want to see the previous value, date it was changed and name of user that changed it; for example, if the previous value is 15% and I change it to 18%, I want the comment to show: 15%, 7/6/09, karrims. In addition, I do not want it to delete the previous comment, just add to it. Clear as mud?!?:bug:

Thanks,
karrims

[Bob Phillips]

Private mcPrev As Variant
Const WS_RANGE As String = "H1" '<== change to suit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = WS_RANGE Then
mcPrev = Target.Text
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Comment Is Nothing Then
.AddComment "Previous values" & Chr(10)
.Comment.Shape.TextFrame.Characters(1, 999).Font.Bold = True
.Comment.Shape.TextFrame.AutoSize = True
Else
.Comment.Text .Comment.Text & mcPrev & ", " & Format(Date, "m/d/yy") & "," & Environ("Username") & Chr(10)
.Comment.Shape.TextFrame.Characters(16, 999).Font.Bold = False
.Comment.Visible = False
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[karrims]

:bow: This is why I love you guys!!! Thank you!!!:bigkiss:

[Bob Phillips]

I had implemented this self-same idea for one of my clients recently, so I already had it in my back pocket :)

[anandbohra]

This code works great for single cell.
Can we modify this for range of cells like A1:D50

I tried to modify this line as stated from


Const WS_RANGE As String = "H1" '<== change to suit

to


Const WS_RANGE As String = "A1:D50" '<== change to suit

it worked fully except catching old text (now it is not able to store old value in cell).

pl help

[Bob Phillips]

Yes, iterate through the range of Target and process each cell.

[anandbohra]

pl explain how to achieve the same :dunno

[antonf]

pl explain how to achieve the same :dunno
Makes two of us noobs... :help

I would like to see how to do it for a:

Range
Column
Entire worksheet