Track Changes on All Worksheets in One Workbook
Using similar code, you can also track changes on all worksheets in a given workbook. Like the previous example, this code places the tracked changes on Sheet2 of the workbook. Sheet2 must therefore have a codename of Sheet2 and should be set to xlVeryHidden.
However, this code must be placed in the Workbook module (ThisWorkbook) of the workbook. Right-click on the sheet name tab, choose View Code, double- click on ThisWorkbook in the Project window of the VBE, and paste the following code:
Dim vOldVal 'Must be at top of module Private Sub Workbook_SheetChange(ByVal Sh As Object, 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 = "'" & Sh.Name & "'!" & 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 Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) vOldVal = Target End Sub
Again, exit the VBE and save and close your workbook.
When you open your workbook, make sure you enable macros to run the code, and any changes you make to any of the worksheets in the workbook will be tracked and recorded on Sheet2, except that this code will also record the sheet name as well as the cell reference in column A.