Run time error 1004 - Method 'OnTime' of object'_Application' failed.

[danbates]

Hi, please can someone help me?

I keep getting the following error:

Run time error 1004 - Method 'OnTime' of object'_Application' failed.

It keeps popping up quite often but from different situations on my file but it doesn't always happen when i repeat a certain situation.

Here is all the workbook code

The offending line is in red.

Option ExplicitConst Timeout As Long = 10 ' 10 minutes


Private Sub Workbook_Open()


Application.OnTime TimeValue("02:00:00"), "sveWrkBk"  '<-- change time to save here hh:mm:ss


dTime = Time
On Error Resume Next
Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe"
On Error GoTo 0
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


On Error Resume Next
[COLOR=#ff0000]Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe", , False[/COLOR]
dTime = Time
Application.OnTime dTime + TimeSerial(0, Timeout, 0), "CloseMe"
On Error GoTo 0


End Sub

Any help would be much appreciated.

Kind Regards

Dan

 

[Domenic]

I haven't fully tested it, and you may need to make some changes to suit your needs, but try the following code that needs to be placed in the code module for ThisWorkbook...

Option Explicit

Const Timeout As Long = 10 ' 10 minutes


Dim dtNextSave As Date
Dim dtNextClose As Date


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    On Error Resume Next
    Application.OnTime dtNextSave, "sveWrkBk", , False
    Application.OnTime dtNextClose, "CloseMe", , False
    On Error GoTo 0
    
End Sub


Private Sub Workbook_Open()


    dtNextSave = Now() + TimeValue("02:00:00")
    Application.OnTime dtNextSave, "sveWrkBk"
    
    dtNextClose = Now() + TimeSerial(0, Timeout, 0)
    Application.OnTime dtNextClose, "CloseMe"
    
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


        On Error Resume Next
        Application.OnTime dtNextClose, "CloseMe", , False
        On Error GoTo 0
        
        dtNextClose = Now() + TimeSerial(0, Timeout, 0)
        Application.OnTime dtNextClose, "CloseMe"
        
End Sub

Hope this helps!