Thanks for your time Dave. I learned a lot and I really appreciate the
tutorial!
Andy

"Dave Peterson" wrote:

First, there was a(nother) typo in that code and a bug:

Instead of telling you what to change, here's the revised code:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 15
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run
Dim FirstTime As Boolean
Sub StartTimer()
If FirstTime Then
'changed for today + 8:30 AM
RunWhen = Date + TimeSerial(8, 30, 0)
Else
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
End If
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=True
End Sub
Sub The_Sub()
MsgBox "hi " & Time
'''''''''''''''''
' Your Code Here
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(15, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub
Sub StopTimer()
'useful for testing to stop any scheduled macro
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=False
End Sub
Sub Auto_Open()
FirstTime = True
'Changed for 8:30 AM
If Time TimeSerial(8, 30, 0) Then
FirstTime = False
End If
Call StartTimer
FirstTime = False
End Sub

========

And this is the portion that does what you're asking about.

Sub StartTimer()
If FirstTime Then
RunWhen = Date + TimeSerial(8, 30, 0)
Else
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
End If
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=True
End Sub
and
Sub Auto_Open()
FirstTime = True
If Time TimeSerial(8, 30, 0) Then
FirstTime = False
End If
Call StartTimer
FirstTime = False
End Sub

So when the workbook opens, it looks at the time in the Auto_Open routine. If
it's after 8:30 AM, it says to pretend that starting the timer isn't the first
time.

Then in the StartTimer, it looks at that firsttime variable. If firsttime is
true (before 8:30 AM), then turn on the timer so that it runs at the next 8:30.
If firsttime is false, it just says to run in 15 seconds.




andy wrote:

Excellent! It works perfectly!
Clarification: I should close this file by midnight each night. And, if I
open this file before 830am will it still work the same? As of now, I open
this file before 830am so that it runs while I am at work. Thanks!

"Dave Peterson" wrote:

<<snipped