19. VBA Tips - Log An Audit Trail

Nick's picture


Logging an audit trail of your changes can be useful, and can be done with a small amount of VBA... 

Here's our data:

log-an-audit-trail

Here's a screen shot of what we're trying to achieve:

log-an-audit-trail

Here's the code to do it (put in the worksheet's macro module):

log-an-audit-trail

 

Explanation:

  1. PreviousValue is a variable that we Dim outside the sub routines so that it's available to both routines
  2. 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
  3. 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. 
  4. 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:

AttachmentSize
log-an-audit-trail.xls40 KB

Audit Trail Code Help

Hi Nick,

I was wondering if there is a way to toggle on/off this code while other macros are running. In my current workbook, I have to run a series of macros before I want the audit trail code to start.

Prior to the audit trail code, my macros are copying and pasting data into the spreadsheet (which is blank) that will eventually need to have changes tracked. But currently my macros break and I receive a "Type mismatch" error on this line of code:

If Target.Value <> PreviousValue Then

Thanks for your help!

Nick's picture

application.enableevents =

application.enableevents = false ' turn off events

Run Your Code

application.enableevents = true ' don't forget to turn events back on

about to save registered cell only

Hello sir,
Thanks for your code, very nice. Im very new in vb script.
Im using excell to make food order. Ive just to put how many people want to eat & date when i want the food to deliver.

Your code is helpfull, but it will logging all the changes in the sheet. Can you help me, how to make a log for cell that we want only?

Sorry my language sir.

Hi Nick, first of all i want

Hi Nick,

first of all i want to thank you for the great job your are doing here and for the chance you give to unexperience vba users like myself to learn from experienced members like yourself.

I am kinda new in vba so i hope i don't offend you with my questions.

I tried your code from above in one of my excel tables and I recieved a Runtime error 13. Someone mentioned to me that my data from the excel table in the specific worksheet may not be properly. Could you help me please?

And the secnd question: How do I say when I make a change in my table: for example I add a new line in my table and insert some new name in the blank cell. I mean instead "Bran changed cell B2 with "Name"...Bran added the new line 50 (for example) and changed the cell A50 from Blank to "Something"

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

Almir's picture

Use Track Changes History Sheet

Use Track Changes History Sheet - history can not be deleted from the shared workbook.

Almir's picture

Use Track Changes History Sheet

Use Track Changes History Sheet - history can not be deleted from the shared workbook.

Almir's picture

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

adding time and date?

Any chance that a time and date can be added to this marco?

Thanks