19. VBA Tips - Log An Audit Trail
Logging an audit trail of your changes can be useful, and can be done with a small amount of VBA...
Here's our data:
Here's a screen shot of what we're trying to achieve:
Here's the code to do it (put in the worksheet's macro module):
- PreviousValue is a variable that we Dim outside the sub routines so that it's available to both routines
- When you select a different cell, PreviousValue is set to the value of the cell that you have selected
- This is set via the Worksheet_SelectionChange event
- When you change a cell's value, the Worksheet_Change sub routine is run and it compares the new value (Target.value) with PreviousValue... logging if there has been a change.
- The Log sheet contains details of the changes including the name of the person who changed the cell, what cell was changed, and what it went from and to.
Training Video on how to Log An Audit Trail in Excel: