Need help with Application.Ontime (Cant get it to Stop)

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
Hi,

i have the following

StartTimer is actually called from button within a Userform if it matters

VBA Code:
Public Sub StartTimer()
    Loader
     RunWhen = Now + TimeValue("00:01:00")
     Application.OnTime EarliestTime:=RunWhen, Procedure:="StartTimer", Schedule:=True
     
     Debug.Print "STARTED"
End Sub

Loader is the sub with all the code that runs and updates captions etc... on the Userform

Below is also assigned to a button in userform but doesnt stop anything
VBA Code:
Public Sub StopTimer()

  On Error Resume Next
  Application.OnTime EarliestTime:=RunWhen, Procedure:="StartTimer", Schedule:=False
  On Error GoTo 0
  
  Debug.Print "STOPPED"
  
End Sub


appreciate any help
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Declare RunWhen at the very top of the userform code module so it can be shared between procedures within the module.

VBA Code:
Dim RunWhen  As Date
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
Declare RunWhen at the very top of the userform code module so it can be shared between procedures within the module.

VBA Code:
Dim RunWhen  As Date

Tried but after stopping it still starts up again a minute later
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,501

ADVERTISEMENT

If you followed the suggestion made by @AlphaFrog, it should work. Maybe it's a previously set routine? In any case, first quit Excel completely. Then the code should be as follows...

VBA Code:
Dim RunWhen  As Date

Public Sub StartTimer()
    Loader
     RunWhen = Now + TimeValue("00:01:00")
     Application.OnTime EarliestTime:=RunWhen, Procedure:="StartTimer", Schedule:=True
   
     Debug.Print "STARTED"
End Sub

Public Sub StopTimer()

  On Error Resume Next
  Application.OnTime EarliestTime:=RunWhen, Procedure:="StartTimer", Schedule:=False
  On Error GoTo 0

  Debug.Print "STOPPED"

End Sub

Does this help?
 
Solution

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
If you followed the suggestion made by @AlphaFrog, it should work. Maybe it's a previously set routine? In any case, first quit Excel completely. Then the code should be as follows...

VBA Code:
Dim RunWhen  As Date

Public Sub StartTimer()
    Loader
     RunWhen = Now + TimeValue("00:01:00")
     Application.OnTime EarliestTime:=RunWhen, Procedure:="StartTimer", Schedule:=True
  
     Debug.Print "STARTED"
End Sub

Public Sub StopTimer()

  On Error Resume Next
  Application.OnTime EarliestTime:=RunWhen, Procedure:="StartTimer", Schedule:=False
  On Error GoTo 0

  Debug.Print "STOPPED"

End Sub

Does this help?

Yes this is working thankyou
 

Watch MrExcel Video

Forum statistics

Threads
1,129,664
Messages
5,637,643
Members
416,979
Latest member
CapeCon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top