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
Else
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

Sheets(“LogDetails”).Columns(“A:D”).AutoFit
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:

11 thoughts on “Track Changes Automatically in Worksheet with VBA

  1. Torben

    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

    Reply
  2. Torben

    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

    Reply
  3. DAmil Pizarro

    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.

    Reply
    1. Parth Jetani

      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.

      Reply
  4. Katy

    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?

    Reply
  5. Patricia

    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.

    Reply
  6. Erfan

    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.

    Erfan

    Reply
  7. E

    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!

    Reply

Leave a Reply

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