Track Changes In Excel

One of the main reasons that I use Microsoft Excel is to keep lists to share with multiple people. Microsoft Word has great versioning and colabarative editing capabilities, Excel not so much. The main problem I have is knowing when a particular row has been changed and who changed it.

The following macro implements this.

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Application.Range("TrackChangesOn")(1).Value = "Yes" Then
    If Intersect(Target, Range("HeaderRows, TrackingColumns")) Is Nothing Then
        For Each aCell In Target
            'MsgBox "You just changed " & aCell.Row & " " & Application.UserName
            'MsgBox "cells " & Range("TrackingColumns").Column & ":" & aCell.Row
    
            Cells(aCell.Row, Range("UpdateDate").Column).Value = Date
            Cells(aCell.Row, Range("UpdateBy").Column).Value = Application.UserName
        Next
    End If
End If
 
End Sub

https://github.com/RossGoodman/Excel/blob/master/TrackChanges.bas

This macro needs to go into the spreadsheet on change handler : Alt+F11 to go to the macro editor, select “worksheet” on the top left drop down and “Change” on the top right drop down.

This macro uses a number of named ranges to control how it works.

The first named range “TrackChangesOn” should point to a cell containing “Yes” or “No” to control if the macro runs or not. Sometimes if I’m doing bulk updates I don’t want all the rows to be tagged to I switch this off without affecting other macros. I like to have a “Metadata” sheet with various flags like this.

The next named range is called “HeaderRows”, this should describe any header rows (or any other rows) which should be excluded from track changes. ie If you rename a column header you don’t want to flag this. An example would be”‘WorksheetName’!$1$1″.

The macro will update two columns of data to track the name of the editor and the date of the change. If you manually change this data it should be excluded from the macro so we create a named range called “TrackingColumns”. An example would be “‘WorksheetName’!$O:$P”.

If any cells are changed that are not a header row or a tracking column then we want to record the name and date, to make this flexible we need a further two named ranges: “UpdateDate” and “UpdateBy”, these would contain values such as “‘WorksheetName’!$O:$O” and “‘WorksheetName’!%P:$P”.

You may have to set your “User Name” in Excel – Excel Options | Popular | User Name.

Let me know how you get on.