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: , , , , , , , , , , , , ,
35 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?

  5. Julien says:

    Hi Tom,

    thanks for the code it’s work great except i noticed the code does not work if i drag down data in more than one box at the same time do you know how to fix this ?

    • Tom Urtis says:

      This line…
      [code]If .Address <> “$A$1” Then Exit Sub[/code]
      …limits the example to changes to, and a comment for, cell A1.

      The example code I posted would not do what you are asking about. You’d need a different code to identify all the cells that were dragged onto (hence changed), and loop through those cells to update their respective comments. I suggest asking that on an Excel forum. Your question gives me an inspiration for a future blog post that I can do later this year.

      • Julien says:

        Thanks for your answer
        i already remove that line
        [code]If .Address “$A$1” Then Exit Sub[/code]
        to make it work everywhere in the sheet but of course i still have the drag down problem

        also i suggest to add username with this modification to the code:

        [code]”Set to ” & strNewText & ” On ” & Format(VBA.Now, “MM/DD/YYYY at h:MM AM/PM”) & ” by ” & Application.UserName & Chr(10)[/code]

        • Tom Urtis says:

          As I said, it’s not just that one line of code. Each cell that was dragged onto needs to be looped through so that each value in each changed cell can be represented in that cell’s respective comment.

  6. Jason says:

    Tom this is great!!! Just what I was looking for!!!

    Just one question. Is there a way to have the comment add the original data that was in the cell (1st entered data) to track the audit trail?

    • Tom Urtis says:

      Thanks, Jason.

      As to your question, isn’t that what the code already does? See the pictured sequence where every edit to the cell, including the cell’s first entry, is recorded and shown in the comment after it (the first entry) is made and after each change thereafter.

  7. Jason says:

    When I add the first entry and change it, example: 123456 changes to 987654, it will start the capture at 987654 and not say the first entry was 123456.

    • Tom Urtis says:

      Something else is going on here.
      As you can see in the picture, the first cell entry showed up in the comment.
      Maybe you are entering something into a cell before you have installed the event code, and afterwards, with data already in a cell, you have installed the event code. Is that what is going on?

  8. Jason says:

    This is the code I am using, “to not start tracking until the original data is changed”. Is there a way to have it also track what was originally there? The first entry?

    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 Target.Cells.CountLarge = 1000 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”) & ”by” & Application.UserName & Chr(10) & strNewText
    .Comment.Shape.TextFrame.AutoSize = True
    End With
    Target.Offset(1).Activate
    End Sub

    • Tom Urtis says:

      There is an easy workaround to what you are asking about.

      When I originally posted this example, the idea was to have the code in place before anything was entered into cells that the code is monitoring.

      What you are asking about is how to get around the situation where data exists in a cell, and THEN you install the code.

      In that case, all you need to do AFTER data was first entered, and AFTER you install the code, but BEFORE you change the original cell’s contents, is to select that cell, hit the F2 key and then hit Enter. That will trigger the Change event and you will have the full history of cell changes, starting with the first originally entered data.

  9. Jason says:

    And I agree with Sameh, there has to be a way for you to be compensated for this support! 🙂

    • Tom Urtis says:

      That’s very kind, thank you.

      I will be releasing my Excel test next month, named XAT (an acronym for Excel Aptitude Test), and the best compensation I can think of is to take a look at it (I will offer it at no charge for the first week or so) and then sing its praises wherever you may roam. Seriously, I am really excited about my test, it is the best Excel test anywhere offered by anyone, and I do mean by anyone including Microsoft’s and anyone’s. I’ll announce its release on my social media channels (Twitter, LinkedIn, and my Facebook business pages), so look for XAT in August!!

  10. Jason says:

    Thanks Tom, however what I am looking for is:
    Cell is Empty (No Comment)
    Data is Inputted (No Comment on First Data Entered)
    Data is Changed (Shows Comment. What was there first and what it is now changed to.)
    Data Changed Again (Continues to Show Audit Trail in Comment)

    Everything ins the code works great, however its not showing what was entered first. Currently it is starting the comment on the first “change to” not “changed from to”.

    Anyway of doing this?

    Again thanks for the help in advance!!!

    • Tom Urtis says:

      Still not clear but getting closer.

      You wrote:
      “Data is Inputted (No Comment on First Data Entered)
      Data is Changed (Shows Comment. What was there first and what it is now changed to.)”

      So what you are saying is you want to enter something in a cell for the first time but not make a comment to show that. But, after you change what was first entered, in other words, starting with the second entry to that cell, you want to begin the string of comments, starting with what was first in the cell followed by the first edit. That means 2 things:
      (1) The first comment that shows up will have 2 entries in it: the original entry and the first edited entry.
      (2) If (1) is correct, there would need to be a time and date stored somewhere for when the first entry was made. The comment code does that now for edits and changes, but not in consideration for what I think you are asking about.

  11. Jason says:

    You are 100% correct!!! 🙂

  12. Jason says:

    Is there anyway to do that?
    – Data is added into a blank cell for the first time (do nothing) like it is now.
    – Once that data has changed (pull in the original data into the comment to show what it “was”) and
    start the audit trail from there?

  13. Jason says:

    I have a workbook with many of pages, so currently I have it added into all sheets and going through the cells to 1000. If Target.Cells.CountLarge = 1000 Then Exit Sub

    • Tom Urtis says:

      No idea what this means as it relates to what you are asking about updating a comment:
      If Target.Cells.CountLarge = 1000 Then Exit Sub

      My question was this:
      Is there a particular cell, or a particular range of cells, where you want this to be the case.

      An answer to my question could be:
      “Yes Tom, cell A1 only, on Sheet3.”
      or
      “Yes Tom, range J7:J25 on every sheet.”
      or
      “Yes Tom, all of row 38 on Sheet2 and Sheet5.”
      or
      “Yes Tom, every cell on every worksheet.”

      So, please take another stab at reading my question and telling me an answer that I would comprehend from an Excel point of view.

  14. Jason says:

    Thanks Tom.

    “Yes, range A1:K:100 on every sheet”

  15. Jason says:

    Or better yet, every cell every worksheet.

  16. Jason says:

    I have figured out a way to have the “previous” data show-up in the comments, however am looking to see if I can continue to add additional comments instead of replacing them. You first will have to go to “Tools” in VBA, “References” and put a Check Mark in “Microsoft Script Run Time”, then press Okay.

    Can you look at this code and see how I can add additional comments, instead of replacing them?

    Here is the code:

    Dim xRg As Range
    Dim xChangeRg As Range
    Dim xDependRg As Range
    Dim xDic As New Dictionary
    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
    If OldVal = “” Then Exit Sub
    Dim I As Long
    Dim xCell As Range
    Dim xHeader As String
    Dim xCommText As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    xHeader = “Previous value :” & preValue & Chr(10) & “Revised ” & Format _
    (Date, “mm-dd-yyyy”) & Chr(10) & “By ” & Application.UserName
    For I = 0 To UBound(xDic.Keys)
    Set xCell = Range(xDic.Keys(I))
    If Not xCell.Comment Is Nothing Then xCell.Comment.Delete
    With xCell
    .AddComment
    .Comment.Visible = False
    .Comment.Text xHeader & vbCrLf & xDic.Items(I)
    End With
    Next
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim I, J As Long
    Dim xRgArea As Range
    On Error GoTo Label1
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Set xDependRg = Target.Dependents
    If xDependRg Is Nothing Then GoTo Label1
    If Not xDependRg Is Nothing Then
    Set xDependRg = Intersect(xDependRg, Range(“A:Z”))
    End If
    Label1:
    Set xRg = Intersect(Target, Range(“A:Z”))
    If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
    Set xChangeRg = Union(xRg, xDependRg)
    ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
    Set xChangeRg = xDependRg
    ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
    Set xChangeRg = xRg
    Else
    Application.EnableEvents = True
    Exit Sub
    End If
    xDic.RemoveAll
    For I = 1 To xChangeRg.Areas.Count
    Set xRgArea = xChangeRg.Areas(I)
    For J = 1 To xRgArea.Count
    xDic.Add xRgArea(J).Address, xRgArea(J).Text
    Next
    Next
    Set xChangeRg = Nothing
    Set xRg = Nothing
    Set xDependRg = Nothing
    Application.EnableEvents = True
    End Sub

Leave a Reply

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

*