Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell

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
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
13 comments on “Tom’s Tutorials For Excel: Using a Comment to Log Changes in a Cell
  1. Chris says:

    I love this code! The only problem I have is that it is restricted to one specified cell. Is there any tweak that would allow this to work over a range of cells, say an entire column?

    • Tom Urtis says:

      Say you want to use any cell in column C except for cell C1 which is a header row. Column C is the third column from the left, meaning it is regarded by VBA as Columns(3).

      The very first line of code in that procedure would be
      If Target.Column <> 3 Then Exit Sub.

      Also, instead of a particular cell such as A1 in my example, rule out all of row 1 with this replacement code line:
      If Target.Row = 1 Then Exit Sub

      That should accomplish what I think you are asking about

  2. Sameh says:

    I’ve been searching for two days now to find something not even perfect as your code and I couldn’t find any. Your code is awesome! I started tweaking it a bit to work for my need and so far so good. I just noticed a couple of things and since I’m not a VBA expert I’m not sure how to solve this.

    1. How can I make it ignore the first entry in the cell? I want it to start tracking if I changed the first entry.
    2. I noticed that once I enter a value in the cell, I can’t use “Undo”. So if I made mistake, I’ll have to re-enter the correct value which will result in an additional entry in the tracking comment.
    3. This is the most important request. How can I make it record the old and the new value instead of the new one only? EX: A1 has the value 100 then I changed it to 200. I want to comment to be something like (04/29/2018: Changed from 100 to 200).

    If you don’t mind, email me your PayPal ID so I can send a little gift appreciating your time and effort.

    • Tom Urtis says:

      Hello Sameh, thank you for your kind comment.

      Regarding your first question, you can place this block of code immediately after the procedure name:

      Dim OldVal$, NewVal$
      OldVal = ""
      Application.EnableEvents = False
      NewVal = Target.Value
      Application.Undo
      OldVal = Target.Value
      Target.Value = NewVal
      Application.EnableEvents = True
      If OldVal = "" Then Exit Sub

      Regarding your second question, Excel loses its Undo stack when a worksheet level procedure is executed. There is nothing that can be done about that.

      Regarding your third question, your example would only be practical for the first change. When new data is entered in the target cell each time, it would be impractical to compile the log of changes any better than how I have those changes recorded in the comment. If your intention is to only care about, and reference, the last 2 changes, then that would defeat the purpose of keeping track of all the changes which was the basic objective of this code to begin with.

      Thank you for offering payment but I supply Excel information on my blog at no charge. I will be developing interactive workbook examples with actual code and formulas that I’ll be putting on my website’s Store page later this year, for which there will be a small charge.

      • Sameh says:

        Hi Tom,

        I managed to use the old value of the cell by using (OldVal) because I really need it in this worksheet and it worked but unfortunately, I noticed it doesn’t perceive the cell value format (EX: if the old value was $1000 it will be recorded in the comment as 1000 not $1000). Why is that?

        How can I perceive the cell value format in the comment?

        • Tom Urtis says:

          The format only makes a value look a certain way. Excel and VBA cares about what the actual underlying value really is. So if you are entering a number and you want it to look like currency, the code needs to format that value as the currency type you want, including decimal places and comma thousands separators, depending on how you want it to look.

          • Sameh says:

            Hi Tom. I think it’s more about the old value was saved as a variable in your code because the “new” value perceives the format just fine. (EX: if I changed the value from $2,000 to $1,000 the comment will be more like “Changed from 2000 to $1,000”. New value always perceives the cell format while the old value doesn’t. That what made me ask you. I figured if it’s working with the new value just fine why not the old one?

  3. Sameh says:

    Hi Tom,

    Thank you so much for your quick response. Your new code works perfect but unfortunately, now when I hit “Enter” after I add a value to a cell, Excel doesn’t behave like it normally behaves by moving the Focus to the cell below it. It stays focused on the same cell I’m currently editing. The rest of the worksheet behave normally. Just the column that we specify in the code works that way.

    I added your new code right below “If Target.Column 3 Then Exit Sub”. I also tried it below “If IsEmpty(Target) Then Exit Sub”. In both cases, it works just fine but still behaves the same way by staying focused in the cell I’m editing.

    I know you offer the codes and the help for free but I want to send a gift as an appreciation for your time and effort. Also, by saving me a lot of time after using this awesome code. Please, I insist.

    • Tom Urtis says:

      Hello Sameh, thank you for again offering payment, but I have answered thousands of Excel questions like this over the years, on my website here and on various Excel message boards, and I do it as a free service. I will be selling Excel tools later this year on my website, so how about I email you when my Store page is ready.

      Now to your question, this line of code…
      Target.Offset(1).Activate
      …should be inserted in 2 places.
      One place is immediately after the line
      Application.EnableEvents = True
      and the other place is at the very end, immediately above the line
      End Sub

      Therefore, this would be the full procedure for your purposes:


      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim OldVal$, NewVal$
      OldVal = ""
      Application.EnableEvents = False
      NewVal = Target.Value
      Application.Undo
      OldVal = Target.Value
      Target.Value = NewVal
      Application.EnableEvents = True
      Target.Offset(1).Activate
      If OldVal = "" Then Exit Sub
      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
      Target.Offset(1).Activate
      End Sub

      • Sameh says:

        Thanks again for the help. It worked. Now when I try to save my file it pops up the following error:
        “The following features cannot be saved in macro-free workbooks:
        – VB project”

        I remember yesterday I saved the file normally like I do without receiving this error but I might be wrong. Is this a must? Should I save it as xlsm file or there is any other way around?

        Sorry for all the questions.

  4. Sameh says:

    Tom, can I make it add the comment to a different cell other than the one that has been changed?

    EX: If the value in D2 changed, the comment will be added to B2 instead of D2?

Leave a Reply

Your email address will not be published. Required fields are marked *

*