GS has brought this to us :
After serious thinking MB wrote :
I get the following error when I close the spreadsheet
Run Time error 1004
Method on time of object _application failed
Help!
here's the code in debug
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("00:00:10"), _
Procedu="Total", Schedule:=False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "Total"
End Sub
Why are you putting a delay when to run "Total" at shutdown if there's no
schedule as to when? VBA doesn't wait for OnTime statements; it just executes
them at the time specified. In this case that happens after the workbook has
already closed and so the error is raised because the macro is not available
any longer.
I'd just run the macro like this:
Call Total
Ok, I get it! I now see you started OnTime at startup and are just
turning it off here. My understanding of using this function is that
the time AND procedure must match. I suggest you store your startup
time in a variable and use that as the value for EarliestTime:
'**Place in Declarations section of standard module**
Public OnTimeStart
Private Sub Workbook_Open()
OnTimeStart = Now() + TimeValue("00:00:10")
Application.OnTime OnTimeStart, "Total"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime _
EarliestTime:=OnTimeStart, Procedu="Total", Schedule:=False
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc