How to keep track of changes in multiple columns in a MS Excel spreadsheet

In a question to my blog article “How to keep track of changes in a MS Excel spreadsheet” I have been asked whether the code I showed there 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 ...
 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

Advertisements

3 Responses to “How to keep track of changes in multiple columns in a MS Excel spreadsheet”

  1. Shatakshi Says:

    Hi,
    Thank you so much for your help, it’s a lifesaver. I really appreciate.

  2. dvanallen Says:

    Axel, this is great stuff. Can you help me understand . . .what if I had a spreadsheet with 100 columns in it but only wanted to log changes for 6 of those columns (e.g., columns X thru AC). What would that look like?

    Would I need to add logic to the line, “For each c in Target”?

  3. amagard Says:

    Hi dvanallen,
    without having tried this …

    You probably don’t wan’t to change this line; target is the input to the worksheet change event handler . I would do something like this:

    For Each c In Target
    Row = c.Row
    Col = c.Column
    If Col <= 6 then
    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
    End If


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: