Timer Event

[Saxman]

What is the best way to get some code to run at timed intervals?

I have used the following code.

Application.OnTime Now + TimeValue("00:05:00"), _ "time_set",
TimeValue("22:00:00")

This should run every 5 minutes until 22:00 Hours.

I have also tried:-

Application.OnTime Now + TimeValue("00:05:00"), "time_set", ,False

I get debugging errors with both.

Would a Do_Loop be better?

TIA

 

[Chip Pearson]

To cancel an OnTime timer, you must pass it the EXACT time that was set in
the code that called OnTime. You should create a module level Double
variable, store the time in that variable, and then pass that value when you
cancel the timer.

See www.cpearson.com/excel/ontime.htm for details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Saxman]

To cancel an OnTime timer, you must pass it the EXACT time that was set in
the code that called OnTime. You should create a module level Double
variable, store the time in that variable, and then pass that value when you
cancel the timer.

I get constant errors when running this. Do I have to alter the values
in the Sub StartTimer() routine?
............................................................
Public RunWhen As Double
Public Const cRunIntervalSeconds = 30 ' 30 seconds
Public Const cRunWhat = "The_Sub"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Private Sub Worksheet_Calculate()
Application.EnableEvents = False

Worksheets("Data").Range("F5:F10").Copy
Worksheets("Archive").Range("A2").Insert Shift:=xlDown

Application.EnableEvents = True
StartTimer
End Sub
.....................................................................

Sorry, but I'm very green at this.

Thanks.