Selected Answer
Here is the procedure that's at the heart of the system I am about to introduce to you.
Sub RecordPriceChange()
' 164
Dim Source As Variant
Dim Target As Range
Dim C As Long
' change the name of the worksheet to the actual name
' of the sheet on which you want the record kept
With Worksheets("TXL_5067")
Source = .Range("D18:D19").Value
Set Target = .Cells(.Rows.Count, "L").End(xlUp).Resize(1, 2)
LastRecord = Target.Value
For C = 1 To UBound(Source)
If Source(C, 1) <> LastRecord(1, C) Then
Target.Offset(1).Value = Application.Transpose(Source)
End If
Next C
End With
SetSchedule
End Sub
It needs a lot of support. Please follow my instructions very carefully. You can check what you are doing against the installation in the attached workbook.
The above procedure must be installed in a standard code module, along with the declarations and procedures you find below.
Option Explicit
Dim NextExecution As Double ' time of execution
Dim LastRecord As Variant ' last data recorded
Sub SetSchedule()
' 164
' set the time interval: 00:01:00 = 0 hours, 1 minute, 0 seconds
NextExecution = Now() + TimeValue("00:01:00")
Application.OnTime NextExecution, "RecordPriceChange"
End Sub
Sub EndSchedule()
' 164
On Error Resume Next
Application.OnTime NextExecution, "RecordPriceChange", , False
End Sub
Make sure that the declarations are at the top of the code module as you find them in the attached workbook (module TXL_5067). Let the main procedure be at the bottom.
To explain: The procedure SetSchedule sets the time when the Sub RecordPriceChange should be run, for example, 1 minute from NOW(). Excel's Timer will run the procedure after 1 minute.
RecordPriceChange checks the last entry in columns L:M against D18:D19 and does nothing if they are the same. But if one of them was changed it will record the new values. It will then call SetSchedule again and that will repeat the cycle after 1 minute.
This cycle will never stop. To interrupt it you have the procedure EndSchedule. When that procedure is run the cycle will end.
I have installed two methods to automate this system. One is linked to opening and closing the workbook. You will find the code below in the ThisWorkbook code module.
Option Explicit
Private Sub Workbook_Open()
' 164
RecordPriceChange
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' 164
EndSchedule
End Sub
The first procedure runs when the workbook is opened and calls RecordPriceChange. The second one runs before the workbook is closed and stops the cycle.
The second method is controlled by the button on the worksheet. It has the caption "Start recording" and calls RecordPriceChange when clicked. At the same time the caption of the button is changed to "Stop recording" and its function will be to call EndSchedule when clicked next time.
The button uses the code below which must be in the code module of the worksheet on which the button resides (which doesn't have to be the one on which you have the action).
Option Explicit
Private Sub CommandButton1_Click()
' 164
With CommandButton1
If .Caption = "Start recording" Then
RecordPriceChange
.Caption = "Stop recording"
Else
.Caption = "Start recording"
EndSchedule
End If
End With
End Sub
I don't think you will keep both systems. Therefore I didn't synchronize the button caption with the system status. If the system was started by the Workbook_Open procedure the button caption will still be "Start recording". But pressing the button to change the caption will do no harm. It will just delay the next execution a few seconds.