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

Track changes in multiple cols in a Excel sheet




I have been asked whether the code I showed in my blog posting "How to keep track of changes in a MSExcel sheet" could be changed to monitor multiple columns in a spreadsheet.

Of course it can. The new code I am showing below handles an arbitrary number of columns, with the following assumptions / pre-requisites:




I have changed my example from the previous article and added an additional column to my list of products: ‘Vendor’, here is an example:



Let’s change Price of Product C to $ 1301,00 and Vendor to ‘Company B’. Here is how the new Change History looks like:



Column B now shows which columns was changed and column C the new value, as usually together with a time stamp. Column A identifies for what product ( my ‘id’ column in this example ) the change occurred.

And here is the new code:

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 ...
 Do
    r = r + 1
 Loop Until IsEmpty(AuditRecord.Cells(r, 1))
 ' For each cell modified ...
 For Each c In Target
   Row = c.Row
   Col = c.Column
   Value = c.Value
   ' ... update Change History with value and time stamp of modification
   AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(1, 1) & " " & Worksheets("Products").Cells(Row, 1)
   AuditRecord.Cells(r, 2) = Worksheets("Products").Cells(1, Col)
   AuditRecord.Cells(r, 3) = Value
   AuditRecord.Cells(r, 4).NumberFormat = "dd mm yyyy hh:mm:ss"
   AuditRecord.Cells(r, 4).Value = Now
   r = r + 1
 Next
End Sub




2 blog comments below

Fantastic amagard!
standready on Thu Oct 16, 2014 11:13 pm
Definitely very helpful thanks Amagard! Cool
deanhills on Thu Oct 16, 2014 11:17 pm



FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.