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 modulePrivate Sub Worksheet_Change(ByVal Target 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", "DATE OFCHANGE")End IfWith .Cells(.Rows.Count, 1).End(xlUp)(2, 1).Value = 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 Worksheet_SelectionChange(ByVal Target As Range)vOldVal = TargetEnd 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.