Track and Report Changes in Excel
To overcome the limitations of the Track Changes feature, you can employ some help from Excel VBA and Excel's Change Events feature.
If you want to track any changes that either you or someone else has made to your data, you can use Excel's Track Changes feature, under Review → Changes (pre-2007, Tools → Track Changes). However, by doing it this way this function has a couple of drawbacks. When Track Changes is enabled, you are forced to share the workbook, whether you want to or not. Also, Track Changes makes many standard Excel features unavailable. We can easily conquer this problem with some code.
Be aware that this code is designed to track and record user changes only one cell at a time.
Track Changes on a Particular Worksheet
For this to work, you need to have a workbook with two worksheets. Sheet1 contains the data to which you want to track and record any changes made. Sheet 2 will contain a list of the tracked changes when we run the code.
To track user changes on a single worksheet, place the following code in the Private module of the worksheet where you would like changes tracked and logged (remember, we have used Sheet1). To get there easily, right-click on the sheet name tab, choose View Code, and paste the following code:
Dim vOldVal 'Must be at top of module Private Sub Worksheet_Change(ByVal Target As Range) Dim bBold As Boolean If Target.Cells.Count > 1 Then Exit Sub On Error Resume Next With Application .ScreenUpdating = False .EnableEvents = False End With If IsEmpty(vOldVal) Then vOldVal = "Empty Cell" bBold = Target.HasFormula With Sheet2 .Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE") End If With .Cells(.Rows.Count, 1).End(xlUp)(2, 1) .Value = Target.Address .Offset(0, 1) = vOldVal With .Offset(0, 2) If bBold = True Then .ClearComments .AddComment.Text Text:= _ "OzGrid.com:" & Chr(10) & "" & Chr(10) & _ "Bold values are the results of formulas" End If .Value = Target .Font.Bold = bBold End With .Offset(0, 3) = Time .Offset(0, 4) = Date End With .Cells.Columns.AutoFit .Protect Password:="Secret" End With vOldVal = vbNullString With Application .ScreenUpdating = True .EnableEvents = True End With On Error GoTo 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) vOldVal = Target End Sub
Now double-click on Sheet2 in the VBA Project window. Ensure Sheet2 has a Sheet CodeName of Sheet2, which will be shown next to Name in the Properties window of the VBE. This worksheet should also be set to xlVeryHidden by selecting it from the drop-down menu next to Visible in the Properties window of the VBE. This will make sure that other users are not able to modify the report.
The code also protects Sheet2 with the password Secret. While worksheet protection is applied to Sheet2, Excel's worksheet protection is rather weak, so hiding of the sheet is an added measure, especially if you lock the Visual Basic Editor, which will ensure macro code is not visible to end users and to a point protects your intellectual property.
Exit the VBE and save your workbook.
Next time you open your workbook and make any changes to Sheet1, the changes will be recorded in Sheet2. Remember, though, you can only unhide Sheet2 by setting the Visible property of Sheet2 to xlSheetVisible.