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:
- Column label in row 1
- Row identifier (id) in column 1 of each row
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:
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
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
2 blog comments below
standready on Thu Oct 16, 2014 11:13 pm
Definitely very helpful thanks Amagard!
deanhills on Thu Oct 16, 2014 11:17 pm