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:

22 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.

      1. DAmil

        Hi J and Shan,
        I was having the same “Runtime error 9, subscript out of range” message when I Copy & Pasted the code into the VB Editor. I deleted it all and followed the video step-by-step retyping it along with Dinesh. First time I ran it I had the same error but then noticed I typed (“logdetail”) instead of (“LogDetails”). I corrected that to appear EXACTLY as the sheet name and it executed beautifully.
        My recommendation is retype the code following Dinesh’s video instructions and make sure the sheet names match exactly; that’s what worked for me.

    1. HVR


      I had the same error and after a very long time I realised that copying and pasting the code the quotation marks are not the same; not sure if this is a font/locale issue. The solution is:

      Find all the ” marks in the copies text and replace with “. It seems silly but it is not the same character. Once this is done for both the left and right hand ” (i.e. “LogDetails”) then the code works fine.

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

  5. Scott

    Using this same code, how can I get the previous value. Here is what I have, but is not working:

    Public OldVal As String

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    OldVal = Target.Value
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If ActiveSheet.Name “Log” Then
    Application.EnableEvents = False
    Sheets(“Log”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name
    Sheets(“Log”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Address(0, 0)
    Sheets(“Log”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 2).Value = OldVal
    Sheets(“Log”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 3).Value = Target.Value
    Sheets(“Log”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 4).Value = Environ(“username”)
    Sheets(“Log”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 5).Value = Now
    Application.EnableEvents = True
    End If

    End Sub

    1. SuperWam

      Hi Scott,
      I don’t know if you finally sorted your problem out but, after struggling for a while, here is what worked for me to display the old value:

      In ThisWorkBook, at the very top, 1st line, declare :
      Dim OldVal as Variant

      and then, still in ThisWorkBook

      – For the definition of OldVal:
      Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

      OldVal = Target.Value

      End Sub

      – For placing the values in your Logbook:
      Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

      If activesheet.Name “LogDetails” And activesheet.Name “Test” And activesheet.Name “Front Page” And activesheet.Name “Front Sheet” Then

      Application.EnableEvents = False
      Application.ScreenUpdating = False

      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = activesheet.Name

      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Address(0, 0)

      If OldVal = “” Then
      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 6).Value = “”
      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 6).Value = OldVal
      End If

      If Target.Value = “” Then
      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 7).Value = “”
      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 7).Value = Target.Value
      End If

      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 8).Value = Application.UserName

      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 9).Value = Date

      Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 10).Value = Time

      Application.EnableEvents = True

      End If
      Application.ScreenUpdating = True
      End Sub

      Might not be perfect as I am a beginner in VBA but it works for me.

      Hope this helps.


  6. Wega

    When Copy & Paste code into the VB editor doasnt’t work, correct or retype again all quotation marks (” ” “). If they are diferent on left or right side of the word it couses problems.

  7. Raj


    This method is fantastic and I use to track changes successfully.
    However It fail to track other user changes on excel.
    Please help me resolve this.

  8. Anthony

    The code really works fast with ad Excel sheet setup as a range, but it doesn’t work with table in Excel. What would I have to change for it to work with a Table,

  9. Angelo Pietroforte

    Code works great, but doesn’t work for checkboxes. I need to get it to work for checkboxes that we have scattered throughout our spreadsheet. I have studied other code examples from internet, but can’t get any to work. I even read the entire Events And Event Procedures in VBA, but am not an advanced enough user to implement. Seems like it should be just a few lines of extra code or another sub. I even tried assigning a macro to the checkbox, as per one of the internet suggestions, but no luck. Please assist.

  10. Charles

    Thank you for posting this code… what code would you insert so that it highlighted the changed cell on screen?


Leave a Reply

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