How to stop a procedure with Application.Ontime

[oldfisher]

Hi guys,

I've had a procedure with Application.Ontime, following are the codes:

Sub OnTime()
ThisWorkbook.RefreshAll
Application.OnTime earliesttime:=(Now + TimeValue("00:00:05")), procedure:="OnTime"
End Sub

Once the procedure gets started, It seems that it can't be stopped properly.

How can I stop it from running?

Thanks

 

[jkpieterse]

Check out Help for the OnTime method, it has an argument to unschedule. For that to work, you must store the last scheduled time. Example:

Option Explicit

Dim nextTime As Double

Sub OnTime()
    ThisWorkbook.RefreshAll
    nextTime = Now + TimeValue("00:00:05")
    Application.OnTime earliesttime:=nextTime, procedure:="OnTime"
End Sub

Sub CancelTimer()
    On Error Resume Next 'In case no items were scheduled
    
    'These must match a previously set OnTime:
    '                                ||||||||              ||||||
    '                                VVVVVVVV              VVVVVV
    Application.OnTime earliesttime:=nextTime, procedure:="OnTime", schedule:=False
End Sub

 

[oldfisher]

Hi Jan,

Many many thanks to you!

Your codes work wonderfully!

The issue get resolved.

Best regards

Lam