Starting and stopping code

Mitch_Morgan

New Member
Joined
Dec 6, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I am new to VBA coding and I have read all the threads that seem to cover this but I am doing something wrong. I have built a time loop simulation that I would like to start with a command button and stop. The simulation code all resides in one module "Main". To create the loop I have the following

VBA Code:
Sub Timer()
    Dim Time_loop
    Time_loop = Now() + TimeValue("00:00:01")
    Application.OnTime Time_loop, "Main"


End Sub

The last line in the last Sub I have Timer to cause it to loop. This works fine. I have inserted a form command button and assigned the macro "Timer".

The stop code is

Code:
Public Sub StopTimer()
    Application.OnTime Time_loop, "Timer", schedule:=False
End Sub

I receive a Run-time error '1004' Method 'OnTime of object'_Application Failed

I have read this same error message occuring in other threads but the fixes I have tried do not work
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
OK, got the answer from one of the Great Gurus of VBA, Chip Pearson, now deceased.

To use the Schedule:=False argument to cancel an OnTime
procedure, you must provide OnTime with the *exact* time that the
event was scheduled. See www.cpearson.com/excel/ontime.htm for
details and example code.

since your procedure uses a non specific and varying time as the Earliest Time, you cannot use the Schedule:= False to kill your macro.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Mitch_Morgan

New Member
Joined
Dec 6, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
OK, got the answer from one of the Great Gurus of VBA, Chip Pearson, now deceased.



since your procedure uses a non specific and varying time as the Earliest Time, you cannot use the Schedule:= False to kill your macro.
I really appreciate your help on this. At least I know what I can't use and that is a help
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
OK, Got it! :giggle:

We have been using the wrong procedure name in the Schedule:=False statement. Amazing how this stuff works. Try it like this:
Note that the Time_Loop variable is declared as a public variable at the top of the code module.

Rich (BB code):
Public Time_Loop As Double

Sub Timer()
    Time_Loop = Now + TimeValue("00:00:03")
    Application.OnTime Time_Loop, "Main"
    'Application.OnTime Time_Loop, "Main", Schedule:=False
End Sub

Sub StopTimer()
    Application.OnTime Time_Loop, "Main", Schedule:=False
End Sub

Sub Main()
MsgBox "OK"
Timer
End Sub

Here is a link to Chip's web site where you can read everything for yourself. Pearson Software Consulting (cpearson.com)

BTW, he has articles on just about everything that Excel does.
 

Mitch_Morgan

New Member
Joined
Dec 6, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
THAT ABSOLUTELY DID IT!!!! Thank you for your persistence in this. Truly great!!!
 

Forum statistics

Threads
1,141,707
Messages
5,707,985
Members
421,539
Latest member
zuniBM

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