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.