Application.OnTime won't stop

[kopite2002]

Evening All

I've trawled through many existing posts on this subject, but unfortunately still cannot get my code to stop running. Issue exists where a file (completely unrelated) is already open, and then I open my file that has the Application.OnTime code.

The following code is in the 'ThisWorkbook' Object:

Dim SaveTime As Date

Private Sub Workbook_Open()
    SaveTime = Now + TimeValue("01:00:00")
    Application.WindowState = xlMinimized
    Application.OnTime SaveTime, "SaveMe", True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim xRet As Boolean
        
    xRet = IsDBOpenLocally
    If xRet Then
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.StatusBar = "**** Application is closing. Please wait..."
        If Not DBwb.ReadOnly Then
            Windows(DBFileName).Visible = True
            DBwb.Save
        End If
        CloseDB
        Application.StatusBar = False
        Application.ScreenUpdating = True
    End If
    On Error Resume Next
    Application.OnTime SaveTime, "SaveMe", False
End Sub

And this is the "SaveMe" sub in the Modules:

Sub SaveMe()
    SaveTime = Now + TimeValue("01:00:00")
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    Application.OnTime SaveTime, "SaveMe", True
End Sub

After closing the worksheet with the above code in, but keeping the original file open (so the excel instance is still running), the code will still run as scheduled, therefore re-opening the file. What have I done wrong?

 

[Domenic]

Try declaring the variable SaveTime as Public, and declaring it in your regular module, not ThisWorkbook . . .

Public SaveTime As Date

Hope this helps!

 

[kopite2002]

Try declaring the variable SaveTime as Public, and declaring it in your regular module, not ThisWorkbook . . .

Public SaveTime As Date

Hope this helps!

Thanks Domenic, that has indeed worked. I knew it would be something simple, but been looking at it for too long to see if for myself!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!