At home and work, I spend a lot of time in Microsoft Excel. Most of the important details of my life are stored somewhere in an Excel Workbook.
For some time, I wanted a way to save a log of the changes that were made to some of the workbooks.
But I didn’t make that change!
The primary reason for needing an excel audit log is Shared Workbooks. Inevitably, when groups collaborate in a single workbook, someone makes a change and then when it turns out to be wrong nobody remembers making the change.
I also think it is insightful to see how you use an application. Is there something you do often that could be scripted into efficiency? This logging gives you the peak behind the curtains.
What gets logged?
The code below will store the Date, Time, Username, Machine Name, Sheet Name and Message about each change. Feel free to track other info as needed, or change around the order of columns to suit your needs.
But only I need to see what happened!
Although I didn’t have a need to hide the Log sheet from my users, you might.
If so, use this code to show/hide the Log sheet. (Reference)
Sub ViewLog() Sheets("Log").Visible = True Sheets("Log").Select End Sub Sub HideLog() Sheets("Log").Visible = xlVeryHidden End Sub
Below you’ll find the code that you can pop into one of your modules in the Excel VBA editor (Alt+F11).
Public Function LogChange(Optional Message) Dim StartTime As Double Dim TempArray() As Variant Dim TheRange As Range Application.ScreenUpdating = False ' How Long Does This Take to Run? ' StartTime = Timer ' Redimension temporary array ReDim TempArray(0, 5) ' Which row is this going in? Lastrow = Sheets("Log").UsedRange.Rows.Count + 1 ' Set the destination range FirstCell = "A" & Lastrow LastCell = "F" & Lastrow 'Store the tracked data in an array TempArray(0, 0) = FormatDateTime(Now, vbShortDate) TempArray(0, 1) = FormatDateTime(Now, vbLongTime) TempArray(0, 2) = Environ$("username") TempArray(0, 3) = Environ$("computername") TempArray(0, 4) = ActiveSheet.Name TempArray(0, 5) = Message ' Transfer temporary array to worksheet Set TheRange = Sheets("Log").Range(FirstCell, LastCell) TheRange.Value = TempArray ' Display elapsed time 'MsgBox Format(Timer - StartTime, "00.00") & " seconds" Application.ScreenUpdating = True End Function
In a past iteration of this tracking code, I tried to use Cell.Value assignments. It was horribly slow. Like 2 seconds per change slow. That may not sound like much, but when you try to enter a range of data and after each cell change you have to wait 2 seconds, you’ll want to pull your hair out. Hence the array assignments which reduced the run time from 2 seconds down to about .06 seconds.
I put this code in Excel and nothing is happening.
Well, I wasn’t done yet, but I commend you on your vigor to try this code out!
You can now call this function leveraging various Events (Open, Close, Save, Print, etc) that are baked into Excel. You do this in the ThisWorkbook code section.
First create some variables to store previous and current values. Place this at the top of ThisWorkbook:
Dim PreviousValue As String ' For Logging Dim CurrentValue As String ' For Logging
Then you can choose from the list of Workbook Events to trigger the function.
Example: Log when the file is opened
Private Sub Workbook_Open() LogChange ("Opened " & ActiveWorkbook.Name) End Sub
Tracking Cell Changes
As you’ll see in the sample code at the end of this article, there is some extra code in the Workbook_SheetChange section.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) CurrentValue = "" ' Reset the current value If Sh.Name = "Log" Then Exit Sub On Error Resume Next If Err.Number = 13 Then PreviousValue = 0 Else CurrentValue = Target.Value End If On Error GoTo 0 ' If there is no values, don't run the following. This fixed a custom macro to add 10 formatted blank lines in a detail sheet If PreviousValue = "" And CurrentValue = "" Then Exit Sub If VarType(PreviousValue) = VarType(CurrentValue) Then If CurrentValue <> PreviousValue Then If Err.Number = 13 Then PreviousValue = 0 End If If PreviousValue = "" Then PreviousValue = "EMPTY" End If If CurrentValue = "" Then CurrentValue = "EMPTY" End If LogChange (Target.Address & " changed from " & PreviousValue & " to " & CurrentValue) End If End If PreviousValue = 0 End Sub
If you just plop the LogChange() function call in this section, you’ll get errors when the variant types on the CurrentValue and PreviousValue aren’t the same. You also won’t get any values for what was changed.
That’s what all the extra code is about; error checking and value tracking.
Note the code in Workbook_SheetSelectionChange as well as it is required to get cell changes to log properly.
Okay, I called the function where I wanted it, but I’m getting an error
Well, I didn’t script the creation of your Log sheet, so go create a sheet, rename it Log and put the headers in at the top of the sheet.
I’d recommend you also put an auto filter on the headers so you can quickly drill down in the future.
What’s the Catch?
Of course, this isn’t perfect code, here’s are the issues:
- It doesn’t track copy/paste actions. I do a lot of copying/pasting in these workbooks and I have not found a way to trigger the LogChange() when a paste occurs.
- Same problem for range deletions. If you select an all cells in a sheet and clear the contents, there is no log entry created.If you know how to capture either of these events or other missing events (like formatting changes) , let me know in the comments.
- It doesn’t track Sheet Deletions.
- Because I call the function from the Worksheet BeforeClose and Worksheet BeforeSave events, closing the workbook prompts for a save, even if you just saved it. I don’t remove the save call because it is enlightening to see users who NEVER save the workbook, except when they close it.
- I’m not a programmer. There are probably many other improvements to be made to the code. I should probably re-comment as some of the comments aren’t really all that enlightening.
Even with these issues, I still find it very helpful to see a log of what updates have been made in an Excel workbook.
Rather than force you to try to copy and paste from the above text, you’re better off downloading the sample workbook and copying the code from there.