With help from this group, a MS VB book and trial & error I have created
some code that runs pretty well. My question is about how to guard against
an 'Esc' key press. I'm using the "Application.EnableCancelKey =
xlErrorHandler" command and in the errorhandler I'm putting up a message box
that asks if the user would like to quit the procedure or not (vbYesNo).
The Yes response works fine (exit sub). The No response (resume next)
causes several problems depending on where the code was when 'Esc' was
pressed. A range select error, object not defined error or it will just
finish up as it should. Why wouldn't it just go back and pick up where it
left off every time?

Without seeing your code, it's a little difficult to tell wat's
happening but I suspect you're relying on "Resume next" to do more than
it does.

Resume Next does not mean "Go back and try the same thing again"
it means
"Accept that what happened was OK and carry on from *after* the error"

In your case, if the user has pressed Esc instead of entering a range,
Resume causes your code to continue as if it had had a valid response.

Therefore, you need somehow to obtain a valid range before continung.

This is why generic error handlers rarely work in VBA, except in the
most trivial cases.
A separate handler for each error is usually required so that you can
force the crrect response before continuing.

RJH wrote:
With help from this group, a MS VB book and trial & error I have created
some code that runs pretty well. My question is about how to guard against
an 'Esc' key press. I'm using the "Application.EnableCancelKey =
xlErrorHandler" command and in the errorhandler I'm putting up a message box
that asks if the user would like to quit the procedure or not (vbYesNo).
The Yes response works fine (exit sub). The No response (resume next)
causes several problems depending on where the code was when 'Esc' was
pressed. A range select error, object not defined error or it will just
finish up as it should. Why wouldn't it just go back and pick up where it
left off every time?

Is there a way to simply disable the 'Esc' key while the code is running?
Thanks!

Bob Howard

"Steve Garman" wrote in message
...
Without seeing your code, it's a little difficult to tell wat's
happening but I suspect you're relying on "Resume next" to do more than
it does.

Resume Next does not mean "Go back and try the same thing again"
it means
"Accept that what happened was OK and carry on from *after* the error"

In your case, if the user has pressed Esc instead of entering a range,
Resume causes your code to continue as if it had had a valid response.

Therefore, you need somehow to obtain a valid range before continung.

This is why generic error handlers rarely work in VBA, except in the
most trivial cases.
A separate handler for each error is usually required so that you can
force the crrect response before continuing.

RJH wrote:
With help from this group, a MS VB book and trial & error I have created
some code that runs pretty well. My question is about how to guard
against
an 'Esc' key press. I'm using the "Application.EnableCancelKey =
xlErrorHandler" command and in the errorhandler I'm putting up a message
box
that asks if the user would like to quit the procedure or not (vbYesNo).
The Yes response works fine (exit sub). The No response (resume next)
causes several problems depending on where the code was when 'Esc' was
pressed. A range select error, object not defined error or it will just
finish up as it should. Why wouldn't it just go back and pick up where
it
left off every time?

Take a look at VBA's help for .enablecancelkey once mo

Use this property very carefully. If you use xlDisabled, there's no way to
interrupt a runaway loop or other non – self-terminating code. Likewise, if you
use xlErrorHandler but your error handler always returns using the Resume
statement, there's no way to stop runaway code.

application.enablecancelkey = xldisabled

So you'll want to use it sparingly.




RJH wrote:

Is there a way to simply disable the 'Esc' key while the code is running?
Thanks!

Bob Howard

"Steve Garman" wrote in message
...
Without seeing your code, it's a little difficult to tell wat's
happening but I suspect you're relying on "Resume next" to do more than
it does.

Resume Next does not mean "Go back and try the same thing again"
it means
"Accept that what happened was OK and carry on from *after* the error"

In your case, if the user has pressed Esc instead of entering a range,
Resume causes your code to continue as if it had had a valid response.

Therefore, you need somehow to obtain a valid range before continung.

This is why generic error handlers rarely work in VBA, except in the
most trivial cases.
A separate handler for each error is usually required so that you can
force the crrect response before continuing.

RJH wrote:
With help from this group, a MS VB book and trial & error I have created
some code that runs pretty well. My question is about how to guard
against
an 'Esc' key press. I'm using the "Application.EnableCancelKey =
xlErrorHandler" command and in the errorhandler I'm putting up a message
box
that asks if the user would like to quit the procedure or not (vbYesNo).
The Yes response works fine (exit sub). The No response (resume next)
causes several problems depending on where the code was when 'Esc' was
pressed. A range select error, object not defined error or it will just
finish up as it should. Why wouldn't it just go back and pick up where
it
left off every time?




--

Dave Peterson

Thanks for your help and words of wisdom.
I'll be sure to use it with care.

Thanks again!

Bob Howard


"Dave Peterson" wrote in message
...
Take a look at VBA's help for .enablecancelkey once mo

Use this property very carefully. If you use xlDisabled, there's no way to
interrupt a runaway loop or other non - self-terminating code. Likewise,
if you
use xlErrorHandler but your error handler always returns using the Resume
statement, there's no way to stop runaway code.

application.enablecancelkey = xldisabled

So you'll want to use it sparingly.




RJH wrote:

Is there a way to simply disable the 'Esc' key while the code is
running?
Thanks!

Bob Howard

"Steve Garman" wrote in message
...
Without seeing your code, it's a little difficult to tell wat's
happening but I suspect you're relying on "Resume next" to do more
than
it does.

Resume Next does not mean "Go back and try the same thing again"
it means
"Accept that what happened was OK and carry on from *after* the error"

In your case, if the user has pressed Esc instead of entering a range,
Resume causes your code to continue as if it had had a valid response.

Therefore, you need somehow to obtain a valid range before continung.

This is why generic error handlers rarely work in VBA, except in the
most trivial cases.
A separate handler for each error is usually required so that you can
force the crrect response before continuing.

RJH wrote:
With help from this group, a MS VB book and trial & error I have
created
some code that runs pretty well. My question is about how to guard
against
an 'Esc' key press. I'm using the "Application.EnableCancelKey =
xlErrorHandler" command and in the errorhandler I'm putting up a
message
box
that asks if the user would like to quit the procedure or not
(vbYesNo).
The Yes response works fine (exit sub). The No response (resume
next)
causes several problems depending on where the code was when 'Esc'
was
pressed. A range select error, object not defined error or it will
just
finish up as it should. Why wouldn't it just go back and pick up
where
it
left off every time?




--

Dave Peterson