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 modulePrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByValTarget As Range)Dim bBold As BooleanIf Target.Cells.Count > 1 Then Exit SubOn Error Resume NextWith Application.ScreenUpdating = False.EnableEvents = FalseEnd WithIf IsEmpty(vOldVal) Then vOldVal = "Empty Cell"bBold = Target.HasFormulaWith Sheet2.Unprotect Password:="Secret"If .Range("A1") = vbNullString Then.Range("A1:E1") = Array("CELL CHANGED","OLD VALUE", _"NEW VALUE", "TIME OF CHANGE", "DATEOF CHANGE")End IfWith .Cells(.Rows.Count, 1).End(xlUp)(2, 1).Value = "'" & Sh.Name & "'!" & Target.Address.Offset(0, 1) = vOldValWith .Offset(0, 2)If bBold = True Then.ClearComments.AddComment.Text Text:= _"OzGrid.com:" & Chr(10) & "" &Chr(10) & _"Bold values are the results offormulas"End If.Value = Target.Font.Bold = bBoldEnd With.Offset(0, 3) = Time.Offset(0, 4) = DateEnd With.Cells.Columns.AutoFit.Protect Password:="Secret"End WithvOldVal = vbNullStringWith Application.ScreenUpdating = True.EnableEvents = TrueEnd WithOn Error GoTo 0End SubPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)vOldVal = TargetEnd 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.