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:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AuditRecord As Range
' This is our change history ...
Set AuditRecord = Worksheets("ChangeHistory").Range("A1:B65000")
r = 0
' Now find the end of the Change History to start appending to ...
r = r + 1
Loop Until IsEmpty(AuditRecord.Cells(r, 1))
' For each cell modified ...
For Each c In Target
Value = c.Value
Row = c.Row
' ... update Change History with value and time stamp of modification
AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(Row, 1)
AuditRecord.Cells(r, 2) = Value
AuditRecord.Cells(r, 3).NumberFormat = "dd mm yyyy hh:mm:ss"
AuditRecord.Cells(r, 3).Value = Now
r = r + 1
- 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:
2 blog comments below
Very cool! Thanks for sharing.
standready on Tue Oct 14, 2014 4:43 pm
That's a fantastic tutorial Amagard. I've got one particular worksheet where I can benefit from this as I've been using it continuously over the years, always wondering whether it is the right version. Now all I would need to do is right click the work sheet I've been working with. Cool!
deanhills on Tue Oct 14, 2014 7:58 pm