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:

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

16 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

  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

  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?

  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!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.