MS-Excel / General Formatting

---

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.