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
- 181261 reads
Protect the audit trail sheet in a shared woorkbook
Dear Nick,
I have a kind of issue regarding auditrail, I made an audit trail with a VBA code quite similar to yours. In order to prevent any change in the audittrail I also add a password in the code (with unprotect/protect). However, this code cannot work in a shared workbook which is an issue as it have to be shared...!
Do you have any idea to make it work once shared??
Many thanks in advance for your help!
Cam
Use Track Changes History Sheet
Use Track Changes History Sheet - history can not be deleted from the shared workbook.
Use Track Changes History Sheet
Use Track Changes History Sheet - history can not be deleted from the shared workbook.
Use Track Changes History Sheet
Use Track Changes History Sheet - history can not be deleted from the shared workbook.
Thank you,
this was more than I expected to find. A very nice component to build a full workflow, with status and logging for several files on a network server. If anyone's interested maybe I post the outcome? Cheers!
Best regards
drsthlm
Log an audit trail
I am also having the same problem my workbook is a shared workbook and I want to keep track of the changes and not allow users to change the log sheet. I tried to protect the workbook by my code does not run. If you dont mind sharing show you found a work around this problem could you share please. Thanks
adding time and date?
Any chance that a time and date can be added to this marco?
Thanks
Just add this at the end of code (before "End If")
Just add this at the end of code (before "End If")
& " at: " & Time & " on: " & Date
Same thing, but with formatted time and date:
& " at: " & Format(Time,"hh:mm:ss") & " on: " & Format(Date,"dd/mm/yy")
Audit Changes by Dragging Values using Fill Handle
Is it possible to audit changes where the user has changed values using the fill handle? There is a type mismatch when the fill handle is used.
Great post, many thanks.
we'd very happy to write this
we'd be very happy to write this for you on a consultancy basis..
http://excelexperts.com/contact
tks
Nick