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
- 168730 reads
Is there a way to make this report to a .Log file instead?
I\\\'m looking at this code, and I love what it does, but I\\\'ve been asked if there is a way to make this report to a log file instead of an excel sheet, only because the person that is asking wants to read this outside of the excel sheet, just in case something went wrong and corrupted the sheet there is a possibility to see why that happened.
Thanks.
we\'d very happy to write
we'd be very happy to write this for you on a consultancy basis..
http://excelexperts.com/contact
tks
Nick
writing to access
Hi nik, i have an audit trail which writes changes to an access database, it is all fine, excpept i need to know how to send the previous cell value and previous formula value to the database. It works fine when sending it to another workbook/worksheet, just not to a databse
here is my code so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cn As ADODB.Connection
Dim oCm As ADODB.Command
Dim logDate As String
Dim logTime As String
Dim logAuthor As String
Dim logWorkbook As String
Dim logWorksheet As String
Dim logcellchange As String
Dim logPrev As String
Dim logNew As String
Dim logPrevForm As String
Dim logNewForm As String
Dim sOldAddress As String
Dim vOldValue As Variant
Dim sOldFormula As String
Dim iRecAffected As Integer
logWorkbook = ThisWorkbook.Name
logWorksheet = Sh.Name
logAuthor = Application.UserName
logDate = Format(Now(), "hh:mm:ss")
logTime = Format(Now(), "dd/mmm/yyyy")
logcellchange = Target.Address
'logPrev = Target.Previous.Value
logNew = Target.Value
'logPrevForm = ActiveCell.Value
logNewForm = Target.Formula
If Target.Rows.Count > 1 Then
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\log.accdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn
oCm.CommandText = "Insert Into AuditTrail (Workbook, Worksheet, Author, DateDetails, TimeDetails, ChangedCell, OldValue, NewValue, OldFormula, NewFormula) Values ('" & logWorkbook & "','" & logWorksheet & "','" & logAuthor & "','" & logDate & "','" & logTime & "','" & logcellchange & "','" & sOldAddress & "','" & logNew & "','" & sOldFormula & "','" & logNewForm & "')"
oCm.Execute iRecAffected
End If
If Target.Columns.Count > 1 Then
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\log.accdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn
oCm.CommandText = "Insert Into AuditTrail (Workbook, Worksheet, Author, DateDetails, TimeDetails, ChangedCell, OldValue, NewValue, OldFormula, NewFormula) Values ('" & logWorkbook & "','" & logWorksheet & "','" & logAuthor & "','" & logDate & "','" & logTime & "','" & logcellchange & "','" & sOldAddress & "','" & logNew & "','" & sOldFormula & "','" & logNewForm & "')"
oCm.Execute iRecAffected
End If
End Sub
Auditing a single cell changes
Hi Nick,
We need to audit changes only to a single cell on the spreadsheet. Could you please show the code?
Thank you,
Viki
add this line at the start of
add this line at the start of the logging code:
if target.address <> Range("RangeToMonitor").address then exit sub
where "RangeToMonitor" is the range you're monitoring...
how about monitor a column or
how about monitor a column or range define ? i mean more than single cell
can be done.. it's a bit
can be done.. it's a bit involved... for more info, Request a Quote
Chk this url as
Chk this url as well
http://excelexperts.com/cell-content-change-history-comments
Error Only
Hi,
Anyway I can edit it to log if user entered an invalid data together with the time and data entered?
Thanks.
Update from a Website
Hello.I have a table who's on a website, and when i click to update in excel it says me error 13.Any solution for this bug?Thanks you very much!