Automatically Track Changes in Excel Worksheet Without Sharing Workbook


We can automatically track changes in an Excel worksheet without sharing a workbook using Excel VBA. We describe below the process of tracking changes to a non-shared workbook.
We can have as many sheets in our workbook as required. Let’s say we have sheet1 and another sheet named ‘LogDetails’. In the sheet1 we have our data which we might like to edit. In the LogDetails sheet we have the headers:
Cell A1: Sheet & Cell Reference
Cell B1: Changed To
Cell C1: User
Cell D1: Date & Time

Now click on the Developer tab and select Visual Basic. In the Visual Basic Editor window double click on ‘ThisWorkbook’ and paste the code shown below:

Private Sub Workbook_Open()
Sheets(“LogDetails”).Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If Sheets(“LogDetails”).Visible = xlSheetVisible Then
Sheets(“LogDetails”).Visible = xlSheetVeryHidden
Else
Sheets(“logDetails”).Visible = xlSheetVisible
End If
Target.Offset(1, 1).Select
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> “logDetails” Then
Application.EnableEvents = False
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & “-” & Target.Address(0, 0)
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 2).Value = Environ(“username”)
Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 3).Value = Now
Sheets(“LogDetails”).Columns(“A:D”).AutoFit
Application.EnableEvents = True
End If
End Sub

View the video to quickly learn more about tracking changes to an Excel worksheet without sharing the workbook:

When you restart Excel and open this file you’ll notice that the ‘LogDetails’ sheet is invisible and you cannot unhide it even by going to the Styles tab in the Home tab and under Format just selecting ‘Unhide Sheet…’ as shown in the image below:

Track changes without sharing Excel Workbook
How to track changes in an Excel worksheet without sharing the workbook

When you make changes in any worksheet, these changes will be recorded in the LogDetails worksheet in the appropriate next blank row. Under cell A1 we can observe the name of the sheet and the cell address in which the changes were made. Under the labels in cell B the changes made will be captured. In column C we’ll capture the user-name and in column D the date and time of the changes will be displayed.
In case you wish to view the LogDetails sheet just double click in any cell in any of the available sheets.. Once you have reviewed the changes you can double-click again in any of the cells in any worksheet to make the LogDetails very hidden.
You can also lock the VBA project so that the code is displayed only to the user who has a password. To hide the VBA code we:
1. Click on Tools in the VBE window
2. Select VBA Project properties
3. Select Protection
4. Check the box ‘Lock project for viewing’
5. Input your password
6. Click on OK

We can also protect the LogDetails worksheet and the process for working with such a worksheet using VBA is described in the another training video.

Further reading and reference:
1. Events And Event Procedures In VBA
2. Run VBA Code Macro on Protected Sheet
3. How to Hide Excel VBA Code Using Password

Download a sample file by clicking on the Excel icon: