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
- 184640 reads
Log an audit trail
is it possible for you to publish a code that logging the changes in the multiple cell at the same time instead of a single cell?
VBA log-an-audit-trial
Thank you for the great video and the step by step instrustions. I am using your log-an-audit-trial and it works great...
I have a workbook that is being shared by other users and this is a great tool to keep track of changes.
In the workbook I have a worksheet tab labeled (log)protected so the users can not change it, but when I protect this sheet the code does not run anymore. I have tried to use the unprotect sheet at the start of the code and protect sheet at the end of the code and still I can not get the code to run.
What am I doing wrong? Needing help please.... I am new at vb and I just can not figure out what I am not doing or should be doing to make this work.
Sub WorkOnProtectedSheet()
Sheet("Log").Unprotect password:="FL123"
Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)
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 & " at: " & Time & " on: " & Date
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PreviousValue = Target.Value
End Sub
Sheet("Log").Protect password:="FL123", _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
Contents:=True
audit trail information values in seperates cells
how can i split the information into seperate cells?
for example:
Column A: changed cell
Column B: old value
Column C: new value
Column D: author
Column E: time
Column F: date
love log file code BUT . . SOLVED
it is not showing a log entry when text is deleted, making a cell blank.
Can you help to provide code to show this as well?
Also, we have asked users not to copy and paste as it messes with complicated conditional formatting - is there any way to show if this is being done?
Thanks so much - you're a code angel!
Additional feature
Hi, this macro works great so firstly wanted to thank you. I have made some slight changes with my minimal knowledge, and have also tried adding a feature but to no prevail...
Basically below the inserted line on the AuditLog I wanted to copy the full row that was changed. (purely because the data is constantly being sorted by different users and its difficult to trace the changed data)
Can you offer any help.
Many Thanks
Andy
if data is sorted, make sure
if data is sorted, make sure each row has a unique identifier, and put that in the change log..
so it would say something like:
XXXX changed "ENTRY DATE" from XXX to YYY for entry 12345
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!
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"