View Full Version : Solved: Track Changes in Comment

07-06-2009, 02:44 PM
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:


Bob Phillips
07-06-2009, 02:56 PM
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

.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

Application.EnableEvents = True

End Sub

07-06-2009, 03:04 PM
:bow: This is why I love you guys!!! Thank you!!!:bigkiss:

Bob Phillips
07-06-2009, 03:10 PM
I had implemented this self-same idea for one of my clients recently, so I already had it in my back pocket :)

07-07-2009, 03:45 AM
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


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
07-07-2009, 04:15 AM
Yes, iterate through the range of Target and process each cell.

07-07-2009, 04:51 AM
pl explain how to achieve the same :dunno

10-26-2009, 12:59 PM
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:

Entire worksheet