Solved: 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?!?

Thanks,
karrims

[Bob Phillips]

[vba]

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
[/vba]

[karrims]

This is why I love you guys!!! Thank you!!!

[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 A150

I tried to modify this line as stated from

[VBA]Const WS_RANGE As String = "H1" '<== change to suit[/VBA]

to

[VBA]Const WS_RANGE As String = "A150" '<== change to suit[/VBA]

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

[antonf]

pl explain how to achieve the same

Makes two of us noobs...

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

1. Range
2. Column
3. Entire worksheet