Run-Time Error 1004 when Stopping Application.OnTime Procedure

JGBlue

New Member
Joined
Jun 20, 2017
Messages
4
I wrote a macro that allows a user to time the duration of their call through a UserForm, while they continue to enter data about their call on an excel sheet.
Most of the time the macro works perfectly but occasionally (randomly it seems) I get an error when attempting to pause/stop the OnTime procedure.

I receive the following error:
"Run-time error '1004':
Method 'OnTime' of object '_Application' failed"


Followed by an alert that says:
"Can't execute code in break mode"

Can someone please help me prevent this error?


The UserForm (frmTimer) contains the following code:
Code:
Private Sub cmdCall_Click()
    
[COLOR=#008000]    'START TIMER[/COLOR]
    If cmdCall.Caption = "START" Then
        cmdCall.Caption = "PAUSE"
        CallStarting = True
        CallDuration = 0
        CallStartTime = Now
        Call StartCallTimer
[COLOR=#008000]    'PAUSE TIMER[/COLOR]
    ElseIf cmdCall.Caption = "PAUSE" Then
        cmdCall.Caption = "RESUME"
        Call StopCallTimer
[COLOR=#008000]    'RESUME TIMER[/COLOR]
    ElseIf cmdCall.Caption = "RESUME" Then
        cmdCall.Caption = "PAUSE"
        CallStarting = False
        PrevCallDuration = CallDuration
        CallStartTime = Now
        Call StartCallTimer
    End If

End Sub

The following code is in a module:
Code:
Option Explicit

Public CallDuration As Date, CallStartTime As Date, PrevCallDuration As Date
Public CallStarting As Boolean


Sub StartCallTimer()
    Application.OnTime Now + TimeValue("00:00:01"), "Call_Count"
End Sub

Sub StopCallTimer()
[COLOR=#a9a9a9]     '>>>Error occasionally occurs here[/COLOR]
    Application.OnTime Now + TimeValue("00:00:01"), "Call_Count", Schedule:=False
End Sub


Sub Call_Count()

    Dim CallDisplay As String
    
    If CallStarting = True Then
        CallDuration = Now - CallStartTime
        CallDisplay = Format(CallDuration, "nn:ss")
        frmTimer.lblCallTime.Caption = CallDisplay
        Call StartCallTimer
    Else
        CallDuration = PrevCallDuration + (Now - CallStartTime)
        CallDisplay = Format(CallDuration, "nn:ss")
        frmTimer.lblCallTime.Caption = CallDisplay
        Call StartCallTimer
    End If

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Rich (BB code):
Sub StartCallTimer()
    Application.OnTime Now + TimeValue("00:00:01"), "Call_Count"
End Sub

Sub StopCallTimer()
     '>>>Error occasionally occurs here
    Application.OnTime Now + TimeValue("00:00:01"), "Call_Count", Schedule:=False
End Sub

When you call OnTime...Schedule:=False, the first parameter (time) must be the same as the first parameter when you started the timer.

It is unlikely that Now+TimeValue("00:00:01") is the same in both calls.

Change the code as follows:
Rich (BB code):
Public StopTime As Date

Sub StartCallTimer()
     StopTime = Now + TimeValue("00:00:01")
     Application.OnTime StopTime, "Call_Count"
 End Sub

 Sub StopCallTimer()
     On Error Resume Next
     Application.OnTime StopTime, "Call_Count", Schedule:=False
 End Sub

The On Error statement is always prudent because there is a "race condition": the OnTime event might "pop" before StopCallTimer is physically running.
 
Last edited:
Upvote 0
Thank you joeu2004!
I've tested it out several times and it's working flawlessly!
Greatly appreciate your help. Thank you for your time!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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
Back
Top