Stop Application.Ontime to stop running upon closing so that it won't open back up

    Code
    Sub Refresh()   'This automatically refreshes the time every 10 seconds
    Application.OnTime Now + TimeValue("00:00:10"), "refresh"
    Application.Calculate
End Sub
Sub StopRefresh()
Application.OnTime Now + TimeValue("00:00:10"), "refresh", False
End Sub



    Code
    Private Sub Workbook_Open()
    Call Refresh
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopRefresh
End Sub


    I'm not sure why the stop refresh isn't working. Should I use something different? The refresh is essentially just calculating every 10 seconds and I need this to stop when the workbook closes. Thank you.

    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    Store the scheduled time in a Public variable and use that time to start or stop the timer:

    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    Thanks for the response! So this stops the timer when I run the macro. However, when I close the file, it will open back again if I have another file open too. This makes me wonder if it is stopping it or not when the file is closing. Could it be something wrong with

    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Call StopRefresh 
End Sub

    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    There is a comma missing which means the OnTime Schedule argument is in the wrong position. Try this:

    Code
    Sub StopRefresh()
    On Error Resume Next
    Application.OnTime RunWhen, "refresh", , False
End Sub

    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    I got an error message with that extra comma now. Run-time error 1004 Method 'onTime of object'_application' failed

    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    I can't reproduce your error.


    Have you included the On Error Resume Next? That is needed in case RunWhen is zero, which can happen if you click Reset in the VBA editor or recompile the code.

    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    My mistake, I forgot to add On Error Resume Next and it works now!! Thank you very much!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login