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:

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

Leave a Reply

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