You are invited to Log in or Register a free Frihost Account!

How to keep track of changes in a MSExcel sheet

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
End Sub


    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! Cool
deanhills on Tue Oct 14, 2014 7:58 pm

© 2005-2011 Frihost, forums powered by phpBB.