RE: Stopping macro execution without generating an error message....

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
591
RE: Stopping macro execution without generating an error message....

This code displays a userform every 5 seconds
Code:
       t = Now() + TimeSerial(0, 0, 5)
      Application.OnTime t, "RepeatMSG"

And the macro which displays the form:

Sub RepeatMSG()
     Userform.Show
   t = Now() + TimeSerial(0, 0, 5)
  Application.OnTime t, "RepeatMSG"
End sub

This button on the sheet - the traditional way - stops the code from displaying the form but generates an error message "Run time eror 1004 Method onTime of object Application.onTime failed" , because it "breaks" code execution, not stops or ends it:
Code:
Private Sub CmdStopMsg_Click()
Application.OnTime t, "RepeatMSG", , False
End Sub


There must be a simple way to stop the execution without breaking the code which will
always generate an error message,, but I can't figure it out.
Can anyone's help on this.
Thanks, cr
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Re: Stopping macro execution without generating an error message....

Try this modification to your OnTime code.

Code:
Sub RepeatMSG()
On Error Goto SKIP:
     Userform.Show
   t = Now() + TimeSerial(0, 0, 5)
  Application.OnTime t, "RepeatMSG"
SKIP:
End sub
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
591
Re: Stopping macro execution without generating an error message....

Try this modification to your OnTime code.

Code:
Sub RepeatMSG()
On Error Goto SKIP:
     Userform.Show
   t = Now() + TimeSerial(0, 0, 5)
  Application.OnTime t, "RepeatMSG"
SKIP:
End sub

Hi JLGWhiz
---doesn't work. I just copied and pasted in your code and ran it several times the last 2 hrs. Same result:
at the line of code in the Stop Msg button
Error: "Method onTime of object failed"
Code:
Private Sub CommandButton3_Click()
Application.OnTime t, "RepeatMSG", , False --->yelloew line here
End Sub
Then a msgbox "Csn't execute code in break mode"
which is where I started. There must be a way to stop the recurring userform from displaying other than
breaking the code to generate an error msg. Thx for all your help in helping to solve this. cr
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Re: Stopping macro execution without generating an error message....

OK, I didn't test it, but I thought it might just go to the End Sub. Apparently, the error is being generated in the Button sub, so I had the error handler in the wrong sub anyway.
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
591
Re: Stopping macro execution without generating an error message....

Hi JLGWhiz -
In case you're interested,
Code:
Sub RepeatMSG()
   If Range("STOPREP") <> "" Then
   Exit Sub
   Else
   SHODTPICKER.TextBox1.Value = Sheets("DATA").Range("F2").Value
   SHODTPICKER.Show
   t = Now() + TimeSerial(0, 0, 5)
   Application.OnTime t, "RepeatMSG"
   End If
End Sub
STOPREP is a named range, in this case, cell G1. As long as this cell is blank, the code executes and
RepeatMSG keeps calling itself over and over as given in the Else block. If I put a "1" oranything in
STOPREP the code stops running clean and free of any code break errors. I looked everywhere on
the web and YouTube but NOT ONE person came up with a way to stop an Application.onTIme from
repeating itself when calling it in its own procedure like I have. I tested this for about 5 min. and eveything
seems to work. I want to post this as an offering to anyone out there who may be experiencing the same
issue with stopping a procedure from calling itself that contains the Application.onTIme event.

You've had 10,000+ posts from what I can see, so your level of experience is probably far greater than mine.
So maybe you have comments on this one way or the other.

Let me know if you have any thoughts on this.

Tbx again for all your help.

cr
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Re: Stopping macro execution without generating an error message....

That is being innovative. A simple If...Then...Else statement to turn the procedure off.
Thanks for the feedback,
Regards, JLG
 

Forum statistics

Threads
1,136,205
Messages
5,674,396
Members
419,506
Latest member
mpazr001

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