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
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
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:

Also view this training video on YouTube.

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:

11 thoughts on “Automatically Track Changes in Excel Worksheet Without Sharing Workbook

  1. Tom

    I like this method and have used it to track my changes successfully.
    Would you have a VBA method which takes this one step further by creating a hyperlink back to the cell that the value changed in?
    In your example this would be to create a hyperlink out of the information in column A.
    A friend and I each have copies of the same workbook. I would like to share with him my changes. I think if I can create hyperlinks of the data in column A, and he copies the “LogDetails” worksheet into his copy of the workbook, he could quickly jump to the cell and enter the change I made as shown in column B.

    Thank you

    1. C

      Hi, Great! That was really useful.

      Is it possible to add in a column that shows the previous value that was changed?


  2. E

    Great information! I have two questions. Is there is a way to protect the “Log Details” sheet with code that will unprotect, record changes and protect again? I would like a user to be able to view the sheet but they shouldn’t be able to edit it. I tried to protect the sheet but the VBA will not execute because it is protected.

    I’ve changed the VBA slightly to track changes in a specific sheet however I’d like to also specify a column i.e. sheet “Sheet1” column B:B. Is this possible?

    Thank you!

    1. F

      Thank you very much for your code! It works very well and is a huge improvement to our documentation.

      I have one question. Is it possible to also give Information about the value of the cell that has been changed before it was changed. Like a “changed from” in addition to the “changed to”?

      Thanks in advance

  3. Shan


    I am using Excel 2007. I have followed the steps, but when I reopened the file, the error message is Runtime error 9, subscript out of range.

    When debug, this line is causing the problem: Sheets(“LogDetails”).Visible = xlSheetVeryHidden

    Please advise.

  4. Pingback: Track Changes Automatically in Worksheet with VBA | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *