Run time error

[MB]

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"), _
Procedure:="Total", Schedule:=False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "Total"
End Sub

 

[GS]

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"), _
Procedure:="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

 

[GS]

GS has brought this to us :

After serious thinking MB wrote :

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, Procedure:="Total", Schedule:=False
End Sub