Workbook Open/Before Close events

[lijon]

Hi
I'm going a little crazy with the workbook_open and workbook_beforeclose events in VBA. Could really use some help!
I have a workbook-open event so a macro "Refresher" will run at 10.28AM each day, once someone opens the workbook.
If they close the workbook, the "stoptimer" macro runs, which should cancel the "Refresher" from running at 10.28.
Here's the problem: If I open the workbook before the 10.28am run time, and then close it later in the day, say 5.01pm, I get an error! in the workbook-beforeclose section!
If I open the workbook after 10.28am, then close it sometime later, there's no error! Problem is, this workbook is usually open from start to finish of each day.
Would be grateful for any ideas why. Here's the code:

in the "This Workbook" page, I've got:

Private Sub workbook_open()
Application.OnTime TimeSerial(10, 28, 0), "refresher"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call stoptimer
End Sub


In another Module, I've got:


Private Const cRunWhat = "refresher"

Sub starttimer()
RunWhen = TimeSerial(10, 28, 0)
Application.OnTime RunWhen, cRunWhat, , True
End Sub

Sub stoptimer()
RunWhen = TimeSerial(10, 28, 0)
Application.OnTime RunWhen, cRunWhat, , False
End Sub



Sub refresher()
Dim data As Variant
more code
more code

End Sub

[BrianMH]

what error message do you get?

[GTO]

...Here's the problem: If I open the workbook before the 10.28am run time, and then close it later in the day, say 5.01pm, I get an error! in the workbook-beforeclose section!


If I open the workbook after 10.28am, then close it sometime later, there's no error! Problem is, this workbook is usually open from start to finish of each day.

Hi there,

If you open the workbook before the scheduled time and the procedure is called by OnTime, the OnTime has thus been "used up" so-to-speak. Thus, it errors as it is trying to cancel an OnTime that doesn't exist. Does that make sense?

Try:

Sub stoptimer()
Dim RunWhen As Date

If TimeSerial(1, 45, 0) > Time Then
RunWhen = TimeSerial(1, 45, 0)
Application.OnTime RunWhen, cRunWhat, , False
End If
End Sub

I of course just changed the times to test... Also, you can use On Error Resume Next and On Error GoTo 0 wrapped around the cancelling call.

Hope that helps,

Mark

[lijon]

GTO - thanks much! I think that's exactly it... we'll find out today anyway, but it seemed to run...err..not run. THANKS ALL!