Excel Audit Trail VBA Code

The following is an example of how to implement a simple audit trail within an Excel macro enabled workbook. The aim is to capture changes made to individual cells, so we have a before and after picture of what was changed. In addition to this we are going to record every time the spreadsheet is opened, closed, and saved. All the changes will be logged in an external text file located in the same directory as the workbook that will house the code.

First, in a new or existing Excel workbook create a class called csLogger and add the following code to the class:

Next, go to the ThisWorkbook code module of your workbook and add the following code. If you already have existing code in this module, then add the lines below individually to their respective events:

If you save the workbook, close and then reopen, you should see the code in action as a text file will appear next to where the workbook is saved. You can go ahead and edit some of the cells in the workbook, and your changes should start to show up as log entries in the text file.

Thank you for reading this post. Please take time to read the disclaimer about content found on this site.

Share :Facebooktwitterredditpinterestmail

6 thoughts on “Excel Audit Trail VBA Code”

  1. Hi,
    thanks for the post. It’s super helpful.

    I do have two questions:
    1) can you define the log’s file type? I would prefer to keep it read-only.
    2) It appears that I have the following instance:
    10/12/2020 11:17:45 ,tomasz,OPEN
    10/12/2020 11:29:04 ,tomasz,OPEN
    what can be the issue here?

    Thanks for your help

  2. Hello. This code worked brilliantly, but (?after a recent Excel update) it seems to be not generating an audit logfile any more. The code seems to be running and no errors are produced, but no logfile either. I can’t work out why and wonder if anyone else is having this problem? Would really appreciate help as I’m relying on this code to audit data entry in research. With many thanks, James

  3. Many thanks for this code, but a recent update to Excel seems to have stopped it working. Whilst the code seems to run, no file is created or updated anymore.

Leave a Reply to Rick Cancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.