Did you know that you can keep track of changes made in a MS Excel spreadsheet, thus basically create an audit trail of changes made to a particular range in a particular worksheet ?
Here is how to do it. I have created a little sample spreadsheet listing some products with a price in a worksheet called “Products”. In a change history I want to see what product price was changed to what value when.
In order to do this I first create an extra worksheet named “ChangeHistory”. I define three columns: “Product”, “Price”, “Timestamp”.
Now I write some Visual Basic code for the change event of my “Products” worksheet. The easiest way to invoke the code editor properly is to do a right-click on the tab of my worksheet “Products”, then select “View Code”. In the appearing code editor I select “Worksheet” in the left drop down and “Change” in the right drop down. This takes me to a sub routine called “Worksheet_Change” into which I type in the following code:
1: Dim AuditRecord As Range
2: ' This is our change history ...
3: Set AuditRecord = Worksheets("ChangeHistory").Range("A1:B65000")
4: r = 0
5: ' Now find the end of the Change History to start appending to ...
6: Do
7: r = r + 1
8: Loop Until IsEmpty(AuditRecord.Cells(r, 1))
9: ' For each cell modified ...
10: For Each c In Target
11: Value = c.Value
12: Row = c.Row
13: ' ... update Change History with value and time stamp of modification
14: AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(Row, 1)
15: AuditRecord.Cells(r, 2) = Value
16: AuditRecord.Cells(r, 3).NumberFormat = "dd mm yyyy hh:mm:ss"
17: AuditRecord.Cells(r, 3).Value = Now
18: r = r + 1
19: Next
Note:
- Target is the range of changed cells as an input parameter to this sub routine,
- Line 14 needs to be modified for a different worksheet name; here I grab the value from column 1 of my changed range as a label ( here: product ) of the item changed.
And here is how it works. Suppose we have the following initial list of products:
Now we make the following changes:
- We copy the price for product B to C and D
- We change price for product G to $ 4.100.
Thus we end up with this list:
If we check out our change history it reflects nicely what has been changed to what new value when:
April 15, 2011 at 8:22 am
Really like this – thank you for posting it. I presume it is also possible to track what they have changed the figure from. Is it also possible to ID who made the change to a shared workbook and force them to give a reason for the change?
April 15, 2011 at 4:36 pm
Hi Simon,
getting the old value might be a bit tricky, since the change event for a worksheet is fired once the value has been changed.
Logging the user should be easy by using the Application.UserName property, as for instance described here.
Forcing input of a reason for a change might be possible as well by launching some input dialog from the handler of the worksheet change event.
April 15, 2011 at 5:38 pm
…..so the first event would be the cells in the defined range changing from blank – so able to capture there?
The reason for this somewhat overzealous approach is that I have a set of auditing guidelines that say an Excel spreadsheet is not an appropriate way to capture income as cells can be changed – so they want paper capture as changes are (more) obvious. …no,really…!!
Really appreciate your response.
April 17, 2011 at 12:34 pm
Right, if you start with blank cells initially all changes ever made would show up in your change log.
Regarding auditability I am not sure whether coming up with a change log like I described it would satisfy your audit requirements since it is easy to manually overwrite (manipulate) the change log itself.
April 17, 2011 at 12:46 pm
Point well made, but I think if the sheet is locked and/or hidden, that will be safeguard enough for my purposes. One cannot completely defend against a determined attack, but 80-20 rule applies.
July 13, 2011 at 9:10 am
I need a help .. the above coding only apply for 2 col data .. what if I need to track for more than 2 col data ? can you pls help
July 13, 2011 at 1:37 pm
Hi shereen,
in order to monitor more columns you need the following changes:
. you also need to obtain the Column property of c ( from target )
. use that when fetching the value; line 14: .Cells(Row, Column) instead of .Cells(Row, 1)
. have an additional column in your change history so that you can record what column changed. Assuming the column name in row 1 you would get the value for that with .Cells(1,Column)