Home » Blogs » Nick's blog

19. VBA Tips - Log An Audit Trail

Nick's picture
Submitted by Nick on 12 May, 2009 - 17:46


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
»

Re:

Submitted by Anonymous on 2 February, 2012 - 17:06.

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?

Submitted by Anonymous on 18 September, 2011 - 16:20.

The code itself totally makes sense; what I don't understand is the link between typing in the cell and kicking off the sub.

»
Nick's picture

events

Submitted by Nick on 20 September, 2011 - 21:18.

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

Submitted by Anonymous on 18 August, 2011 - 10:54.

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!

»
Nick's picture

logging column insert

Submitted by Nick on 24 August, 2011 - 07:13.

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!

Submitted by Anonymous on 21 July, 2011 - 08:43.
Hi Nick, Great macro, thanks a lot, but I have one little problem. I have spreadsheets which copy all the data from another spreadsheet into this "main" spreadsheet. As the data to be copied is never in the same range (could be more or less lines), the macro I wrote copies everything from columns A - M in my main sheet like this: 
    Sheets("IT Import").Select
    ChDir "V:\DATA\Ent van der"
    Workbooks.Open Filename:="V:\DATA\Ent van der\RAL4V5.XLS"
    Columns("A:M").Select
    Selection.Copy
    ThisWorkbook.Activate
    Application.GoTo Reference:="R1C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A26").Select
    Application.CutCopyMode = False
    Calculate
However, if I let this macro run while your macro is on, it creates a log entry for each and every cell it is changing, which is (roughly estimated) 13 x 65000 lines. Which is a little bit too much... Do you know how I could solve this? Or, alternatively, instead of keeping track of the change of each cell, would it be possible to keep track of just the persons saving information to my "main" sheet? Thanks a lot, Peter
»
Nick's picture

turn off events

Submitted by Nick on 21 July, 2011 - 21:15.
at the start of the code, put: 
Application.enableevents = false
and at the end 
Application.enableevents = true
»

Audit trail for Merged Cells

Submitted by Anonymous on 29 April, 2010 - 13:01.

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

»
Nick's picture

merged cells

Submitted by Nick on 29 April, 2010 - 14:07.

see the answer above

»

Audit Rail

Submitted by Anonymous on 31 August, 2010 - 08:45.

How can i add a date to the Audit trail code given?

»