Stopping repetitive loop execution through user form (or other ide

I have a repetitive loop in a macro that I want to run repeatedly until the
user decides to stop it. I tried having a non-modal userform with a toggle
button which when pushed, it would loop out of the repetitive loop. However,
since the macro is busy in the code, I cannot push the button in the user
form. Does anyone have any ideas on how a user can intervene to stop the code
from the repetitive loop? The userform is just an idea, but anything that
works would be great.

Thanks for any help!
Mike

Post your code in this forum first, maybe some1 may solve this !

thanks,,
Halim


Mike menuliskan:
> I have a repetitive loop in a macro that I want to run repeatedly until the
> user decides to stop it. I tried having a non-modal userform with a toggle
> button which when pushed, it would loop out of the repetitive loop. However,
> since the macro is busy in the code, I cannot push the button in the user
> form. Does anyone have any ideas on how a user can intervene to stop the code
> from the repetitive loop? The userform is just an idea, but anything that
> works would be great.
>
> Thanks for any help!
> Mike

Check Help for: EnableCancelKey

Tim

"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have a repetitive loop in a macro that I want to run repeatedly until the
> user decides to stop it. I tried having a non-modal userform with a toggle
> button which when pushed, it would loop out of the repetitive loop.
> However,
> since the macro is busy in the code, I cannot push the button in the user
> form. Does anyone have any ideas on how a user can intervene to stop the
> code
> from the repetitive loop? The userform is just an idea, but anything that
> works would be great.
>
> Thanks for any help!
> Mike

Hit the escape key

--
Regards,
Tom Ogilvy

"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have a repetitive loop in a macro that I want to run repeatedly until the
> user decides to stop it. I tried having a non-modal userform with a toggle
> button which when pushed, it would loop out of the repetitive loop.
> However,
> since the macro is busy in the code, I cannot push the button in the user
> form. Does anyone have any ideas on how a user can intervene to stop the
> code
> from the repetitive loop? The userform is just an idea, but anything that
> works would be great.
>
> Thanks for any help!
> Mike

This is almost exactly what I need! If I hit the ESC key and need to finish
up my loop (for some cleanup), is there a way to go back into the loop and
finish up the current iteration. Essentially, I need to set a trigger
"GetOutOfLoop" by pushing the ESC key and continue at the current line until
the end of the loop.

Here are the notes from the help about EnableCancelKey:
On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler
MsgBox "This may take a long time: press ESC to cancel"
For x = 1 To 1000000 ' Do something 1,000,000 times (long!)
' do something here
Next x

handleCancel:
If Err = 18 Then
MsgBox "You cancelled"
End If


"Tim Williams" wrote:

>
> Check Help for: EnableCancelKey
>
> Tim
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> >I have a repetitive loop in a macro that I want to run repeatedly until the
> > user decides to stop it. I tried having a non-modal userform with a toggle
> > button which when pushed, it would loop out of the repetitive loop.
> > However,
> > since the macro is busy in the code, I cannot push the button in the user
> > form. Does anyone have any ideas on how a user can intervene to stop the
> > code
> > from the repetitive loop? The userform is just an idea, but anything that
> > works would be great.
> >
> > Thanks for any help!
> > Mike
>
>
>

I found that the following line works well:
Resume Next

This, however, will only go to the next line in the CURRENT procedure. Is
there a way to go back to the current line in any procedure (especially a
CALLED procedure)?

Thanks!

"Mike" wrote:

> This is almost exactly what I need! If I hit the ESC key and need to finish
> up my loop (for some cleanup), is there a way to go back into the loop and
> finish up the current iteration. Essentially, I need to set a trigger
> "GetOutOfLoop" by pushing the ESC key and continue at the current line until
> the end of the loop.
>
> Here are the notes from the help about EnableCancelKey:
> On Error GoTo handleCancel
> Application.EnableCancelKey = xlErrorHandler
> MsgBox "This may take a long time: press ESC to cancel"
> For x = 1 To 1000000 ' Do something 1,000,000 times (long!)
> ' do something here
> Next x
>
> handleCancel:
> If Err = 18 Then
> MsgBox "You cancelled"
> End If
>
>
> "Tim Williams" wrote:
>
> >
> > Check Help for: EnableCancelKey
> >
> > Tim
> >
> > "Mike" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have a repetitive loop in a macro that I want to run repeatedly until the
> > > user decides to stop it. I tried having a non-modal userform with a toggle
> > > button which when pushed, it would loop out of the repetitive loop.
> > > However,
> > > since the macro is busy in the code, I cannot push the button in the user
> > > form. Does anyone have any ideas on how a user can intervene to stop the
> > > code
> > > from the repetitive loop? The userform is just an idea, but anything that
> > > works would be great.
> > >
> > > Thanks for any help!
> > > Mike
> >
> >
> >

You could always set a [global] flag in the error handler, and then use that to signal to other procedures to tidy up and then exit.

--
Tim Williams
Palo Alto, CA


"Mike" <[email protected]> wrote in message news:[email protected]...
> I found that the following line works well:
> Resume Next
>
> This, however, will only go to the next line in the CURRENT procedure. Is
> there a way to go back to the current line in any procedure (especially a
> CALLED procedure)?
>
> Thanks!
>
> "Mike" wrote:
>
> > This is almost exactly what I need! If I hit the ESC key and need to finish
> > up my loop (for some cleanup), is there a way to go back into the loop and
> > finish up the current iteration. Essentially, I need to set a trigger
> > "GetOutOfLoop" by pushing the ESC key and continue at the current line until
> > the end of the loop.
> >
> > Here are the notes from the help about EnableCancelKey:
> > On Error GoTo handleCancel
> > Application.EnableCancelKey = xlErrorHandler
> > MsgBox "This may take a long time: press ESC to cancel"
> > For x = 1 To 1000000 ' Do something 1,000,000 times (long!)
> > ' do something here
> > Next x
> >
> > handleCancel:
> > If Err = 18 Then
> > MsgBox "You cancelled"
> > End If
> >
> >
> > "Tim Williams" wrote:
> >
> > >
> > > Check Help for: EnableCancelKey
> > >
> > > Tim
> > >
> > > "Mike" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I have a repetitive loop in a macro that I want to run repeatedly until the
> > > > user decides to stop it. I tried having a non-modal userform with a toggle
> > > > button which when pushed, it would loop out of the repetitive loop.
> > > > However,
> > > > since the macro is busy in the code, I cannot push the button in the user
> > > > form. Does anyone have any ideas on how a user can intervene to stop the
> > > > code
> > > > from the repetitive loop? The userform is just an idea, but anything that
> > > > works would be great.
> > > >
> > > > Thanks for any help!
> > > > Mike
> > >
> > >
> > >

Tim,

I can use a global flag, but then I would need the Error Handler & Resume
logic in EACH procedure that I call. Is there a way that I can avoid that?

Thanks!

"Tim Williams" wrote:

> You could always set a [global] flag in the error handler, and then use that to signal to other procedures to tidy up and then exit.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Mike" <[email protected]> wrote in message news:[email protected]...
> > I found that the following line works well:
> > Resume Next
> >
> > This, however, will only go to the next line in the CURRENT procedure. Is
> > there a way to go back to the current line in any procedure (especially a
> > CALLED procedure)?
> >
> > Thanks!
> >
> > "Mike" wrote:
> >
> > > This is almost exactly what I need! If I hit the ESC key and need to finish
> > > up my loop (for some cleanup), is there a way to go back into the loop and
> > > finish up the current iteration. Essentially, I need to set a trigger
> > > "GetOutOfLoop" by pushing the ESC key and continue at the current line until
> > > the end of the loop.
> > >
> > > Here are the notes from the help about EnableCancelKey:
> > > On Error GoTo handleCancel
> > > Application.EnableCancelKey = xlErrorHandler
> > > MsgBox "This may take a long time: press ESC to cancel"
> > > For x = 1 To 1000000 ' Do something 1,000,000 times (long!)
> > > ' do something here
> > > Next x
> > >
> > > handleCancel:
> > > If Err = 18 Then
> > > MsgBox "You cancelled"
> > > End If
> > >
> > >
> > > "Tim Williams" wrote:
> > >
> > > >
> > > > Check Help for: EnableCancelKey
> > > >
> > > > Tim
> > > >
> > > > "Mike" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >I have a repetitive loop in a macro that I want to run repeatedly until the
> > > > > user decides to stop it. I tried having a non-modal userform with a toggle
> > > > > button which when pushed, it would loop out of the repetitive loop.
> > > > > However,
> > > > > since the macro is busy in the code, I cannot push the button in the user
> > > > > form. Does anyone have any ideas on how a user can intervene to stop the
> > > > > code
> > > > > from the repetitive loop? The userform is just an idea, but anything that
> > > > > works would be great.
> > > > >
> > > > > Thanks for any help!
> > > > > Mike
> > > >
> > > >
> > > >
>
>
>

I'm not clear on exactly what the issue is. Don't you just have one
procedure which loops?
Or is it calling other procedures from within the loop? If so then yes you
will need an error handler
in each procedure. Or you could let the error "bubble up" to the main
procedure and handle it there.

Tim


"Mike" <[email protected]> wrote in message
news:[email protected]...
> Tim,
>
> I can use a global flag, but then I would need the Error Handler & Resume
> logic in EACH procedure that I call. Is there a way that I can avoid that?
>
> Thanks!
>
> "Tim Williams" wrote:
>
>> You could always set a [global] flag in the error handler, and then use
>> that to signal to other procedures to tidy up and then exit.
>>
>> --
>> Tim Williams
>> Palo Alto, CA
>>
>>
>> "Mike" <[email protected]> wrote in message
>> news:[email protected]...
>> > I found that the following line works well:
>> > Resume Next
>> >
>> > This, however, will only go to the next line in the CURRENT procedure.
>> > Is
>> > there a way to go back to the current line in any procedure (especially
>> > a
>> > CALLED procedure)?
>> >
>> > Thanks!
>> >
>> > "Mike" wrote:
>> >
>> > > This is almost exactly what I need! If I hit the ESC key and need to
>> > > finish
>> > > up my loop (for some cleanup), is there a way to go back into the
>> > > loop and
>> > > finish up the current iteration. Essentially, I need to set a trigger
>> > > "GetOutOfLoop" by pushing the ESC key and continue at the current
>> > > line until
>> > > the end of the loop.
>> > >
>> > > Here are the notes from the help about EnableCancelKey:
>> > > On Error GoTo handleCancel
>> > > Application.EnableCancelKey = xlErrorHandler
>> > > MsgBox "This may take a long time: press ESC to cancel"
>> > > For x = 1 To 1000000 ' Do something 1,000,000 times (long!)
>> > > ' do something here
>> > > Next x
>> > >
>> > > handleCancel:
>> > > If Err = 18 Then
>> > > MsgBox "You cancelled"
>> > > End If
>> > >
>> > >
>> > > "Tim Williams" wrote:
>> > >
>> > > >
>> > > > Check Help for: EnableCancelKey
>> > > >
>> > > > Tim
>> > > >
>> > > > "Mike" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > >I have a repetitive loop in a macro that I want to run repeatedly
>> > > > >until the
>> > > > > user decides to stop it. I tried having a non-modal userform with
>> > > > > a toggle
>> > > > > button which when pushed, it would loop out of the repetitive
>> > > > > loop.
>> > > > > However,
>> > > > > since the macro is busy in the code, I cannot push the button in
>> > > > > the user
>> > > > > form. Does anyone have any ideas on how a user can intervene to
>> > > > > stop the
>> > > > > code
>> > > > > from the repetitive loop? The userform is just an idea, but
>> > > > > anything that
>> > > > > works would be great.
>> > > > >
>> > > > > Thanks for any help!
>> > > > > Mike
>> > > >
>> > > >
>> > > >
>>
>>
>>