lijon
04-14-2011, 11:57 AM
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
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