An audit trail for data entry in Excel using VBA

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