Need Help on Tracking Changes in Excel (Data logger-VBA)

[chobeo31]

I want to track any modify or change in my set a given of data. After quite a time figuring on my own, nothing works as I expected.

The data set is ranged between A1:G6 on "Sheet1". I assume that any new entry would follow the order.

	A	B	C	D	E	F	G
1	Order ID	Client	Time	Type	Product	Quantity	Price
2	1234	aa	2014/12/23 12:00	buy	ABC	2	3250
3	1235	bb	2014/12/24 12:00	sell	XYZ	1	3221
4	1236	cc	2014/12/21 12:00	buy	XYZ	5	3260
5	1237	dd	2014/12/23 12:00	sell	ABC	7	3150

<tbody>
</tbody>

Now I want to track any modified or changed affect the data set and view that on "Sheet2".
For example, if F2 changed into 6 and G5 changed into 3155, respectively, on "Sheet2", 2 entries recorded like this

	A	B	C	D	E
1	Date and Time	Order ID	Comment	Old	New
2	2014/12/23 12:15	1234	Quantity modified	2	6
3	2014/12/23 12:17	1237	Price modified	3150	3155

<tbody>
</tbody>

Any suggestion?
Please help a newbie in VBA.
Thanks a lot.

 

[ParamRay]

.
.

This macro should do the job, but you must place it in the code module corresponding to Sheet1 (otherwise it won't work):

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim rngTrackRange As Range
  Dim varHeaders    As Variant
  Dim rngSelection  As Range
  Dim rngTrackCell  As Range
  Dim varOldVal     As Variant
  Dim lngLogRow     As Long
  Dim intColIndex   As Integer
  
  With Me
    On Error Resume Next
    Set rngTrackRange = Intersect(Range(.Cells(2, 1), .Cells(6, 7)), Target)
    On Error GoTo 0
    If rngTrackRange Is Nothing Then Exit Sub
    varHeaders = Range(.Cells(1, 1), .Cells(1, 7)).Value
  End With
  Set rngSelection = Selection
  
  Application.EnableEvents = False
  With ThisWorkbook.Worksheets(2)
    For Each rngTrackCell In rngTrackRange
      Application.Undo
      varOldVal = rngTrackCell.Value
      Application.Undo
      lngLogRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
      intColIndex = rngTrackCell.Column
      .Cells(lngLogRow, 1).Value = Now
      .Cells(lngLogRow, 2).Value = Me.Cells(rngTrackCell.Row, 1).Value
      .Cells(lngLogRow, 3).Value = varHeaders(1, intColIndex) & " Modified"
      .Cells(lngLogRow, 4).Value = varOldVal
      .Cells(lngLogRow, 5).Value = rngTrackCell.Value
    Next rngTrackCell
  End With
  Application.EnableEvents = True
  rngSelection.Select

End Sub

 

[chobeo31]

Oh my dear, you are such a saint in this holiday you know. So many grateful.
It works like a miracle.
However, I dont know where the prob is that it worked only a few times before stopping. (May be within 1 min or before autosave, I guess) Do you have the same problem?

 

[chobeo31]

Ahha, I got something. Because of the range (your default is only within cell(6,7)) when I modify beside that range, no log would be recorded; now I fixed it a bit and retest. Till now, it works perfectly just the way I expect. I will tell you the result later.
Many thanks.