Spacebar Exits Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zone - 24 Dec 2006 18:09 GMT

I have a long looping macro I'd like the user to be able to exit by pressing
the Spacebar. I'd like to accomplish this without the big overhead usually
associated with API calls. Any simple (short) way to do this? Probably a
wierd question! But thanks and happy holidays! James

Reply to this Message

Dave Peterson - 24 Dec 2006 18:19 GMT

How about a different key -- the ESC key???

If yes, take a look at Application.EnableCancelKey in VBA's help.

> I have a long looping macro I'd like the user to be able to exit by pressing
> the Spacebar. I'd like to accomplish this without the big overhead usually
> associated with API calls. Any simple (short) way to do this? Probably a
> wierd question! But thanks and happy holidays! James

Signature

Dave Peterson

Reply to this Message

Zone - 24 Dec 2006 19:20 GMT

Dave, Thank you. Help goes to some length to explain that there are
implications to using EnableCancelKey, so apparently it should only be
used under controlled conditions to keep the Esc and Ctrl-Break keys
functional. I thought one way to do this was to use in a function. I
don't know the effect on the error trapping status of the calling
routine, though. See any problem with this code?
Seasons Greetings, James

Sub TryThis()
Dim j As Long
On Error Goto Problem
For j = 1 To 10000000
If LookForEsc() = True Then
MsgBox "You pressed Esc"
Exit Sub
End If
Next j
Problem:
End Sub

Function LookForEsc() As Boolean
LookForEsc = False
On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler
handleCancel:
If Err = 18 Then
LookForEsc = True
End If
End Function

> How about a different key -- the ESC key???
>
[quoted text clipped - 4 lines]
> > associated with API calls. Any simple (short) way to do this? Probably a
> > wierd question! But thanks and happy holidays! James

Reply to this Message

Dave Peterson - 24 Dec 2006 19:39 GMT

You're actually looking for that escape key each cycle in that loop..10000000
times!

Take one more look at that example in help and you'll see that when you hit the
escape key, it interrupts the code preemptively. There's no need to check for
that escape key each time through the loop.

> Dave, Thank you. Help goes to some length to explain that there are
> implications to using EnableCancelKey, so apparently it should only be
[quoted text clipped - 37 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Zone - 24 Dec 2006 21:02 GMT

Dave, thanks for staying with me on this. I do realize that I'm looking for
the Esc key absurdly, but I just wanted to run a test, and this way it
pauses long enough to allow me to hit the Esc key, while interrupting the
delay if I do so, for simplicity. I won't actually be using it this way in
my code. Seems to work fine. My main concern was what effect the
Application.EnableCancelKey would have on the error trapping in the calling
routine. Thanks again, James

> You're actually looking for that escape key each cycle in that
> loop..10000000
[quoted text clipped - 50 lines]
>> >
>> > Dave Peterson

Reply to this Message

Zone - 31 Dec 2006 19:00 GMT

Dave, I don't know if you're monitoring the group right now, but I did want
to follow up on your kind advice. I was unsure of the on error status of
the calling routine, which is very long and goes to many subroutines. I
have also been very sloppy about not putting an On Error GoTo 0 after my
error trapping code in the main routine. As it turns out, I have found that
just pressing Esc and holding it down will eventually break the program in
an orderly manner and present an error screen. Since the program code is
protected, only Continue and End are available on Excel's error screen, so
the user is only presented with one choice to stop the program and really
can't mess up anything. Evidently my error checking is so sloppy that no
error checking is in force at some time, and if I hold Esc down long enough,
the program will break without going uncontrollable. Thanks again. Your
reputation precedes you! Regards, James

> You're actually looking for that escape key each cycle in that
> loop..10000000
[quoted text clipped - 50 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 31 Dec 2006 19:15 GMT

If you're seeing that "code execution has been interrupted" screen, then I don't
think the .enablecancelkey code is doing what you want.

This is just the normal window that pops up when you hit ESCape (or ctrl-break).

But that doesn't seem too bad to me -- for code that only I run, it seems pretty
reasonable.

> Dave, I don't know if you're monitoring the group right now, but I did want
> to follow up on your kind advice. I was unsure of the on error status of
[quoted text clipped - 67 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message