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:

3 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

  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


Leave a Reply

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