October 3, 2016

Track Changes Automatically in Worksheet with VBA

Track Changes Automatically in Worksheet with VBA
Track Changes Automatically in Worksheet with VBA

We can track changes automatically in an Excel worksheet with VBA. We capture the sheet and cell address where the change is made, the old or original value, the changed value, the name of the person who made the change, the date and time the change was made and a back-link to the changed cell address is also created.

Watch the Excel training video:

Watch the video on YouTube.

Here’s the complete VBA code to track changes automatically:

Dim oldValue As String
Dim oldAddress As String
Private Sub Workbook_Open()
Sheets(“LogDetails”).Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If Sheets(“LogDetails”).Visible = xlSheetVisible Then
Sheets(“LogDetails”).Visible = xlSheetVeryHidden
Sheets(“LogDetails”).Visible = xlSheetVisible
End If
Target.Offset(1, 1).Select
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
sSheetName = “Data”
If ActiveSheet.Name <> “LogDetails” Then
Application.EnableEvents = False
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & ” – ” & Target.Address(0, 0)
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ(“username”)
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets(“LogDetails”).Hyperlinks.Add Anchor:=Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 5), Address:=””, SubAddress:=”‘” & sSheetName & “‘!” & oldAddress, TextToDisplay:=oldAddress

Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Value
oldAddress = Target.Address
End Sub

Download a sample file:

40 thoughts on “Track Changes Automatically in Worksheet with VBA

  1. sSheetName = “Data”

    I would do this small change. Because i want to track all data from all my sheet to the logdetails
    the hyperlink would be wrong if i set sSheetName = “Data”

    sSheetName = ActiveSheet.Name

    1. Hi Torden,

      I would like to track all changes made to all of my worksheets and I tried your suggestion because I was having issues with the hyperlink. When I changed it, my macro now breaks at that line. Any ideas why?

  2. Work around this error
    if you got this error when you select more than one cell:
    runtime error “13”
    type mismatch

    when i debug:
    oldValue = Target.Value = 0
    Do this:

    If Target.Count > 1 Then Exit Sub
    If Target.Count = 1 Then
    oldValue = Target.Value
    End If

    1. I keep getting this issue then the macro will work for a handful of changes then it stops and I can’t get the macro to work again, I have to use an old version of my workbook and copy and paste it back into it to get it to work to try to fix the issue but due to my limited knowledge have been unsuccessful. I tried copying and pasting the code above in to help but that stopped the macro working too. Please can someone help?

    2. I am finding Runtime error “5”
      Invalid Procedure or Cell Argument
      When deleting multiple cell values or inserting multiple rows/columns.

      Sheets(“LogDetails”).Hyperlinks.Add Anchor:=Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 5), Address:=””, SubAddress:=”‘” & sSheetName & “‘!” & oldAddress, TextToDisplay:=oldAddress

      Please help.

    3. Hi Torben,
      Thanks for it help. But after modification, if I press Ctrl+A, Run time error 6 appears. Can u pl help.

    4. I am getting this runtime error “13” but I don’t know how to resolve it with your suggestion. What do I need to add to the code and where to stop this from happening?

      1. Hi Laura,

        Add this line directly below the Sub declarations:
        On Error Resume Next
        On Error Exit Sub

  3. Hello Dinesh,
    Just like the other friends that have posted comments, I too am very grateful to you for the videos you publish. Your tutorial is executed excellently, So, thank you again!

    PS: I’m researching how to resolve ‘Subscript out of range (Error 9)’ problem and hope to share with others as soon as I figure it out. Like most coding errors, I’m sure it’s something silly.

    1. Hello Dinesh,

      your video tutorials is helpful to me, thank you

      but i am facing similar problem like DAmil.
      pls help me to rectified “run time error 9 subscript out of range”

      waiting for your replay.

  4. Hi Dinesh, Thank you for making this available, it is fantastic.

    Is there a code to add to this to ignore a specific worksheet in the workbook from tracked changes?

  5. Hello Dinesh,

    Is there a way to show changes to cell formulas in the Log Details Original Value and Change To columns? This would be very helpful for my application.

  6. Hello Dinesh,

    If I would like to transfer multiple data from my Userform to the worksheet, I want to have the auto change tracking data in to the LogDetails sheet. The video that you have shared, data changed in the sheet can be tracked once of every data changed in a cell whereas I want to save the multiple data from the userform which will be saved in multiple cells in a worksheet.

    Would be grateful if you please guide me in this.


  7. Hello,
    Great tutorial and work, thanks a lot.
    What I wanted to ask is, is it possible to log changes in a different workbook, instead of different worksheet in same workbook?

    Thanks so much in advance!

  8. I noticed that the ‘Undo’ option isn’t available when using this tracking VBA. Is there anyway so that the ‘Undo’ option is made available?

      1. Hi, did you get any solution to this? I’m working on a project where I need to enable undo for users of the application.

  9. Thanks for posting! I used this code and am coming up with a Compile Error “user-defined type not defined”, with the following piece of the code highlighted

    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target as Range, Cancel as Booleon)

    My code is in “ThisWorkbook”, so it should be a defined type.

    Any recommendations?

  10. Hello Dinesh,

    Thanks for sharing the code!

    I’m having this error – Subscript out of range (Error 9) on the following line:
    Sheets(“LogDetails”).Visible = xlSheetVeryHidden

    I’ve checked that the sheet name is correct, Googled and tried every suggestion that I came across but the problem persists.

    Would you happen to know what’s the issue?


    1. You have deleted the LogDetails sheet. This sheet is always there even though you cannot see it until you double-click somewhere on the Data sheet. Re-load the sheet from this website and try again without deleting that sheet.

      1. Hi Ted,

        Thanks for your comments. I tried again and it’s still the same error. The LogDetails sheet is hidden and I’m unable to call it out by double-click somewhere on the Data sheet.

        When I tried to create a new sheet with the name “LogDetails”, it says “That name is already taken. Try a different one.”

        However, if change the first few lines of the code to the following:

        Private Sub Workbook_Open()
        Dim Log As Worksheet
        Set Log = ThisWorkbook.Sheets(“LogDetails”)
        Log.Visible = xlSheetVeryHidden
        End Sub

        I can run these lines until the next Sheets(“LogDetails”) is encountered. It seems that i always get stuck at Sheets(“LogDetails”).

        Any idea what is wrong?

        Thanks a lot!

    2. Please check the inverted commas in the code, It seems that there are different inverted commas in the original code, and I copy and paste it.
      I changed all the inverted commas, and now it works without errors.
      thank you for the code.
      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & ” – ” & Target.Address(0, 0)

      Sheets(“LogDetails”) should be: Sheets(“LogDetails”)
      the same is at :
      ” – ” should be: ” – ”
      example 2:
      Address:=””, SubAddress:=”‘” & sSheetName & “‘!”
      should be: Address:=””, SubAddress:=”‘” & sSheetName & “‘!”

  11. Hi Dinesh

    I tried to use this without the hiding funtions, and at first sight it was awesome, was working immediately in the file where I wanted to apply for.

    Later on I realized, there is one specific activity, which kills the script: if the user does a multiple selection on the worksheet, where we want to log. Do you have any idea, how would it be possible to solve this? The easiest option would be to block the multiple selection, with some extra line in the code – Until now I have not found any solution.


  12. Sheets(“LogDetails”).Columns(“A:D”).AutoFit

    I am getting an compile error called Syntax error for the following in (“A:D”). How do I fix it?

    1. change the line:
      The Problem is The inverted commas.
      If its still not working, write the line yourself , do not copy paste.

      1. update:
        Don’t copy paste , write the line yourself.
        when I sent my reply the inverted commas in the second line changed.

  13. Awesome post! It’s just what I needed. However, my code is breaking at the hyperlink line. I have tried typing the code myself and copy and pasting the code. Neither fixed it and I cannot find a solution. Any suggestions?

  14. Hai,

    Hyper link not working, it shows The syntax of this name isn’t correct.

    please guide me.

  15. i tried to use the code on at different excel sheet and it is not working. any help and thanks for the tutorial. thus using the code on a new excel sheet, it does not work. any way to go around it

  16. Thank you Dinesh. This vba code was very helpful. I have a question regarding the Log Details tab. How can I lock down the cells in LogDetails so the information from the that sheet can not be deleted?

    I’ve tried using the following code but no luck.

    Sub ProtectCellsInB()

    Worksheets(“LogDetails”).Cells.Locked = False

    End Sub

    Thanks in advance for your help.

  17. Hi Dinesh .. This code worked pretty fine for me. Is it possible to track comments by a user too? I really need this.. is it possible to do so??

  18. Thank you for making this macro for us all! it’s amazing and I’m employing it along with all the fixes from the comments… only problem… i cannot get the “old” value to display in the second column. I’ve manually entered… it just doesn’t recognize old value – leaves it blank. any ideas?

  19. When I copy the code and try to run it, it dosen’t run. It shows a message with run Macro, but there is no Macro even though i have the code on ThisWorksheet. What am I doing wrong ?

Comments are closed.