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
»

Add date to audit trail

Submitted by Anonymous on 31 August, 2010 - 08:52.
yes, add this line under the existing one: 
Sheets("log").Cells(65000, 1).End(xlUp).Offset(0, 1).Value = Now
this will add date in col 2.
»

This macro is SUPERB!

Submitted by Anonymous on 18 March, 2010 - 19:32.

Is it possible to protect the log worksheet so that users can't modify it? Thanks!

»
Nick's picture

log changes

Submitted by Nick on 19 March, 2010 - 12:10.

yes.. just Record a Macro of protecting and unprotecting the sheet with a password.

unprotect at the beginning, and protect at the end.

 

 

»

Changing multiple cells simultaneously

Submitted by Anonymous on 13 January, 2010 - 17:26.

Great post, by the way.

Is there any way that the code can be changed to handle copying and pasting more than one cell at a time?

Nick

»
Nick's picture

Changing multiple cells simultaneously

Submitted by Nick on 13 January, 2010 - 17:58.
I don't think it's possible because when you do a large copy and paste, you don't always select the cells before hand. Consequently, you wouldn't be able to work out what the previous value was. You could modify the existing code to say what the value has been changed to though: 
Dim PreviousValue
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
        For Each thecell In Target
            Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
                Application.UserName & " changed cell " & Target.Address _
                & " to " & thecell.Value
        Next
        Exit Sub
    End If
 
    If Target.Value <> PreviousValue Then
        Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
            Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
    End If
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

NOTE the result of copying col 1 into col 2:

 

Nick Vivian changed cell $C$8:$C$10 to Nick
Nick Vivian changed cell $C$8:$C$10 to Paul
Nick Vivian changed cell $C$8:$C$10 to Bob

- you get the same cell address

 

»

Changing multiple cells

Submitted by Anonymous on 20 December, 2011 - 17:18.

Hi Nick,
i input this code and tested it and was working great. I now have entered the spreadsheet again and it no longer records the log. It doesn't give me any errors or anything. I've checked the code and it's exactly the same. Do you know why this would be happening? No one has opened it since. Not sure why it would just stop working as the code is still there.

Thanks,

»

Did you enable macros when

Submitted by Anonymous on 20 December, 2011 - 17:34.

Did you enable macros when opening ?
Also, are events enabled.. maybe another bit of VBA turns off events.
- try opening in a new Excel session making sure macros are enabled.

»

Audit trail for Merged Cells

Submitted by Anonymous on 23 April, 2010 - 12:56.

Hi Nick,

Thanks for the codes. They are very helpful.

In adidition 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.

»

merged cells

Submitted by Anonymous on 24 April, 2010 - 19:20.

merged cells count as 1, no ?

You can pick up all the cells in a merged range, use Selection.MergeArea

»

Using selection.mergearea

Submitted by Anonymous on 18 August, 2011 - 11:37.

what is the proper syntax for this? how can we use this? I tried using this by changing this original code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PreviousValue = Target.Value
End Sub

to

Private Sub Worksheet_SelectionMergeArea(ByVal Target As Range)
PreviousValue = Target.Value
End Sub

-- It seemed to work but then it appears that the logs created are always stuck at the PreviousValue returning a 'blank' value.

»