Run time error
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 |
Run time error
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Run time error
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 |
All times are GMT +1. The time now is 05:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com