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):
Explanation:
- 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.
Download sheet to practise how to Log An Audit Trail in Excel
Training Video on how to Log An Audit Trail in Excel:
Attachment | Size |
---|---|
log-an-audit-trail.xls | 40 KB |
»
- Nick's blog
- Login or register to post comments
- 180953 reads
Re:
I have the solution but now why when i update the website table don't write on log the changes?Anybody knows?thanks for your help
what triggers the subroutine?
The code itself totally makes sense; what I don't understand is the link between typing in the cell and kicking off the sub.
events
Excel traps a number of events like opening a workbook, selecting a worksheet, changing a value on a worksheet etc..
This is inbuilt to Excel..
Inserting rows/columns
Hi,
I find your macro quite useful. However, i am having problems when I insert colmns - creates all logs for the created blank cells which is a bit too much. Can the log just say that a new column has been inserted?
Thanks!
logging column insert
the logging procedure is designed for an end-user system... the end user should not be inserting columns.
Recommend excluding events where the range impacted is more than one cell.
At the start of the sub, put:
if Target.rows.count>1 then exit sub
if Target.columns.count>1 then exit sub
Nick
Thx!
turn off events
Application.enableevents = false
Application.enableevents = true
Audit trail for Merged Cells
Hi Nick,
Thanks for the codes. They are very helpful.
In addition to the code where changes to more than one cell at a time is recorded, is it possible to have a code to audit trail 5 cells, which are merged.
Thanks,
Naresh
merged cells
see the answer above
Audit Rail
How can i add a date to the Audit trail code given?