'Esc' handling

[RJH]

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?

 

[Steve Garman]

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]

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

Bob Howard

 

[Dave Peterson]

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

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]

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

Thanks again!

Bob Howard