Hallo Paul_Hossler,
I have a counter running permanently on the sheet, so the NOW() stays live. I need to log only at a certain time, I'm afraid a greater or smaller will have the logging just going on and on. BUT what I have done in the meantime is I have put "YEAR", "MONTH", "HOUR" and "MINUTE" formulae in 4 cells next to the two date/times. I now monitor all 4 criteria and once all 4 match, the code executes. The code I have is:
Private tmr_tm As Date
Private tmr_src As Range
Private tmr_dst As Range
Private Sub tmr()
Do
If tmr_src = 0 Then Exit Sub
tmr_dst = "'" & Format(Now - tmr_tm, "hh:mm:ss")
DoEvents
Loop
End Sub
Private Sub Worksheet_Calculate()
If Range("C7").Value = 1 Then
Range("C3").Select
Selection.Copy
Range("G19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
msgbox "Data Logged"
Range("A1").Select
Application.CutCopyMode = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If tmr_src Is Nothing Then Set tmr_src = Range("a1") '<-- cell to check
If tmr_dst Is Nothing Then Set tmr_dst = Range("a2") '<-- time display
If Target.Address = tmr_dst.Address Then Exit Sub
If Target.Address = tmr_src.Address Or tmr_tm = 0 Then tmr_tm = Now
tmr
If Target.Address = "$A$1" Then
If Range("A1").Value = 1 Then
Range("C6").Select
Selection.Copy
Range("C7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Exit Sub
If Range("A1").Value = 0 Then
Exit Sub
End If
End If
End Sub
all works, BUT, after execution the messagebox does not come up and also if I dare touch A1 to stop the counter, excel crashes...Would you please be so kind to see where I have gone wrong?