How To: Log Activity in Microsoft Excel

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

The Code

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:

  1. 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.
  2. 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.
  3. It doesn’t track Sheet Deletions.
  4. 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.
  5. 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.

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

Click here to download a copy of the sample workbook.

Happy Tracking!

Tags

Was this post helpful?

If you enjoyed or benefited from this post, please consider any of the following:
  • post with your friends
  • Leave a comment below
  • to support this site.

Post Navigation