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:


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:


Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

  1. Greetings,
    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. Hi, Great! That was really useful.

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

      Thanks!

  2. 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. 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. Hi,

    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. 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. Hi,

      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. 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
    Sheets(“Log”).Columns(“A:F”).AutoFit
    Application.EnableEvents = True
    End If

    End Sub

    1. 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)

      Sheets(“LogDetails”).UnProtect
      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 = “”
      Else
      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 = “”
      Else
      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
      Sheets(“LogDetails”).Protect
      Application.ScreenUpdating = True
      End Sub

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

      Hope this helps.

      Regards

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

  6. Hi,

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

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

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

  9. I got this working.

    This goes in sheet named “Data”

    Option Explicit
    Public OldValue As String

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sSheetName As String
    sSheetName = “Data”

    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 = OldValue
    Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
    Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ(“username”)
    Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
    Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 5).Hyperlinks.Add Anchor:=Sheets(“LogDetails”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 5), _
    Address:=””, SubAddress:=sSheetName & “!” & Target.AddressLocal, TextToDisplay:=sSheetName & “!” & Target.AddressLocal
    Sheets(“LogDetails”).Columns(“A:D”).AutoFit
    Application.EnableEvents = False
    Application.EnableEvents = True

    End Sub

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

    ****************************

    This goes in “ThisWorkbook”

    Option Explicit

    Dim oldAddress As String
    Dim OldValue As String

    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) ‘blank out this line…..
    ‘Sub showLogDetails() ‘unblank out this

    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 ‘blank out this line…..
    End Sub

    ****************************
    Regards
    Raghu

  10. Thank you very much – great idea and implementation.

    Just a few notes:
    1. If you try to select multiple cells for example entire row or column it gives debug error – the solution I found is to add “On Error Resume Next” to the sub below:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    oldValue = Target.Value
    oldAddress = Target.Address
    End Sub

    Also is there any way to track adding or deletion of the entire row(s) or column(s) as well as changed formulas

    Thank you again

Leave a Reply

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