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:
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:
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
Hi, Great! That was really useful.
Is it possible to add in a column that shows the previous value that was changed?
Thanks!
did you ever receive or figure out how to keep the old value?
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!
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
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.
I get the same error. Did you ever find a solution?
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.
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.
is there a chance to add a column of the original value?
Dear Ad,
Deeply thanks for your sharing so clearly and in details.
This supports me a lot.
does not work on my file ? i don’t know why.
Kind regards
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
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
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.
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
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,
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.
Angelo again. I’m 99% positive, I designed with ActiveX Control checkboxes.
Thank you for posting this code… what code would you insert so that it highlighted the changed cell on screen?
Hi,
I am using your code but not working. Can you help me?
Absolutely Brilliant piece of code. Thank you.
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
Hello,
What do you mean by “blank out this line”?
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
Hello,
This works brilliantly until I tried to track changes that I do using splin button controls to add or subtract a value in a designated cell where I want to track changes. It does not register as a change even though cell number is changed using this button. It only works when manually typing numbers into the cells. How can I get this to work?
I have been using your tracked changes with history. which works well except when I select a row or column. Then the oldvalue fails. Is there an error routine I can add to handle the issue?
Thank you
excellent work Sir,
I have one question.
i need a vba
if condition is met then copy and paste it to other sheet automatically
or
if condition is met then take a screen shot and save it automatically
Hi,
Thank you so much for this video. I wrote the code but when I click in a cell the Logile does not open, what am I missing?
Thank you.
John