Search code examples
excelvbaeventstimeworksheet

Worksheet change event to trigger with Time

I am looking to run a simple macro to clear contents of cells when time in cell F4 changes to less than seven minutes (this cell is updated by API). Cell F4 has time in hh:mm:ss format. My code is

Sub worksheet_change(ByVal Target As Range)
Application.EnableEvents = True
Set Target = Range("F4")
If Target.Value <= Minute(7) Then
    Call myClearContents
Application.EnableEvents = False
End If
End Sub

This code is in the worksheet and ClearContents macro is in a module. I have done the usual google and stackoverflow search, but no luck. Any ideas why this is not working?

Thanks in advance.

Solution

  • Try this:

    Application.EnableEvents = False
' get F3 time
t = CDate(Cells(3, 6))
' get current time
tNow = TimeValue(Now())
' get the difference in minutes
diff = DateDiff("n", t, tNow)

If diff > 7 Then
    Call ClearContents
End If

Application.EnableEvents = True