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

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

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

Zone wrote:

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

--

Dave Peterson

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
Dave Peterson wrote:
How about a different key -- the ESC key???

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

Zone wrote:

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

--

Dave Peterson

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.



Zone wrote:

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
Dave Peterson wrote:
How about a different key -- the ESC key???

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

Zone wrote:

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

--

Dave Peterson

--

Dave Peterson

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
"Dave Peterson" wrote in message
...
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.



Zone wrote:

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
Dave Peterson wrote:
How about a different key -- the ESC key???

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

Zone wrote:

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

--

Dave Peterson

--

Dave Peterson

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
"Dave Peterson" wrote in message
...
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.



Zone wrote:

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
Dave Peterson wrote:
How about a different key -- the ESC key???

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

Zone wrote:

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

--

Dave Peterson

--

Dave Peterson

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.

Zone wrote:

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
"Dave Peterson" wrote in message
...
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.



Zone wrote:

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
Dave Peterson wrote:
How about a different key -- the ESC key???

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

Zone wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson