On_timer Q

[Sean]

The code below, was taken from Chip Pearsons web site on On-Timers.
How could I tailor it so that the StartTimer only runs at 10:00am each
24 hours but only between two variable dates that are held in Sheet1
A1 & B1?

Thanks


Public RunWhen As Double
Public Const cRunIntervalSeconds = 1440 ' 24 Hours
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub The_Sub()
'''''''''''''''''
' Your Code Here
'''''''''''''''''
StartTimer
End Sub

 

[Bob Phillips]

Public RunWhen As Double
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run

Sub StartTimer()
With Worksheets("Sheet1")
If .Range("A1").Value <= Date - 1 And _
.Range("B1").Value > Date Then
RunWhen = Date + 1 + TimeSerial(10,0,0)
Application.OnTime EarliestTime:=RunWhen, _
Procedure:=cRunWhat, _
Schedule:=True
End If
End With
End Sub

Sub The_Sub()
'''''''''''''''''
' Your Code Here
'''''''''''''''''
StartTimer
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sean]

Public RunWhen As Double
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run

Sub StartTimer()
With Worksheets("Sheet1")
If .Range("A1").Value <= Date - 1 And _
.Range("B1").Value > Date Then
RunWhen = Date + 1 + TimeSerial(10,0,0)
Application.OnTime EarliestTime:=RunWhen, _
Procedure:=cRunWhat, _
Schedule:=True
End If
End With
End Sub

Sub The_Sub()
'''''''''''''''''
' Your Code Here
'''''''''''''''''
StartTimer
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

Bob, many thanks once gain for your reply. Will try this out tomorrow

 

[Sean]

Bob, many thanks once gain for your reply. Will try this out tomorrow- Hide quoted text -

- Show quoted text -

Bob, I've tried it with some very simple Clear Contents code at 08:55
but nothing happened, not sure why. Code as below

Public RunWhen As Double
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run
Sub StartTimer()
With Worksheets("Week")
If .Range("A1").Value <= Date - 1 And _
.Range("A2").Value > Date Then
RunWhen = Date + 1 + TimeSerial(8, 55, 0)
Application.OnTime EarliestTime:=RunWhen, _
Procedure:=cRunWhat, _
Schedule:=True
End If
End With
End Sub
Sub The_Sub()
Sheets("Week").Select
ActiveSheet.Unprotect

Range("C8788").Select
Selection.ClearContents

StartTimer
End Sub