Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell
Here’s an easy way to keep a running log of changes to a cell’s text.
Suppose you want your employees to enter an explanation or description of some kind into a cell regarding a topic on your spreadsheet. Maybe there’s a new product being developed and you’ll utilize cell A1 for the team members to enter their ideas during production.
You want to keep a record of everything entered, without burdening anyone with how to edit existing text or how to add a new comment to a cell. The simpler, the better.
In the picture, over the course of time, new entries are made into cell A1. Although each new entry in A1 overrides pre-existing text, the below code will capture all the text ever entered. There’s also a date and time stamp for each new entry, and an empty line between entries for readability.
To install the code, right-click on your worksheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Then press
Alt+Q to return to your worksheet, and test the code to see how the comment creates and updates itself with every change in text you make to cell A1.
Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Address <> "$A$1" Then Exit Sub If IsEmpty(Target) Then Exit Sub Dim strNewText$, strCommentOld$, strCommentNew$ strNewText = .Text If Not .Comment Is Nothing Then strCommentOld = .Comment.Text & Chr(10) & Chr(10) Else strCommentOld = "" End If On Error Resume Next .Comment.Delete Err.Clear .AddComment .Comment.Visible = False .Comment.Text Text:=strCommentOld & _ Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText .Comment.Shape.TextFrame.AutoSize = True End With End Sub