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

[chazrab]

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

This code displays a userform every 5 seconds

       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:

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

 

[JLGWhiz]

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

Try this modification to your OnTime code.

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

 

[chazrab]

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

Try this modification to your OnTime 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"

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]

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]

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

Hi JLGWhiz -
In case you're interested,

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]

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