Application On Time wont turn off

[Flenley]

Hi

I am using a time method to grab some data every minute and it is working but the problem is I can't turn it off! I have used the example in VB help but to no avail. Here is the code, any suggestions?

Sub Timer_Data()

Application.OnTime Now + TimeValue("00:01:00"), "Timer_Run"

End Sub

Sub End_Timer_Data()

Application.OnTime EarliestTime:=Now + TimeValue("00:01:00"), _
Procedure:="Timer_Run", Schedule:=False

End Sub

Thanks

Angus

 

[Damon Ostrander]

Hi Flenley,

The reason why it is not cancelling is that the set anc cancel times are NOT the same because the Now value will give the time at the instant the cancellation OnTime is called, not the time the OnTime is scheduled. To get the cancellation to work you must ensure that the two times are the same. Here is one way to do it:

Dim SchTime As Date

Sub Timer_Data()

SchTime = Now + TimeValue("00:01:00")
Application.OnTime SchTime, "Timer_Run"

End Sub

Sub End_Timer_Data()

Application.OnTime EarliestTime:=SchTime, _
Procedure:="Timer_Run", Schedule:=False

End Sub

where SchTime is a variable whose scope encompasses both routines so that both can access it, and it retains its value between calls.

 

[Flenley]

Thanks!

Work perfect now.