G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

If I was doing it, I would use a command button that ties to a macro which
simply shuts the program down with Application.Quit. Put a caption on the
button that says EARLY TERMINATION.

Private Sub CommandButton1_Click()
ThisWorkbook.Saved = True
Application.Quit
End Sub

It does not alter the original workbook and can be invoked anywhere there is
a break in the code execution.

"Neptune Dinosaur" wrote:

G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

However, if your code is running continuously, my suggestion sucks!

"Neptune Dinosaur" wrote:

G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

Yeah, I thought of that, but user input is disabled by definiton while the
"demo" macro is running, and I need it to be disabled so that users can't
change the scenario that I am demonstating (so non-modal message boxes aren't
the answer. either). Ctrl-Brk seems to be the only viable interrupt. Can I
harness this neatly?
--
Time is just the thing that keeps everything from happening all at once


"JLGWhiz" wrote:

If I was doing it, I would use a command button that ties to a macro which
simply shuts the program down with Application.Quit. Put a caption on the
button that says EARLY TERMINATION.

Private Sub CommandButton1_Click()
ThisWorkbook.Saved = True
Application.Quit
End Sub

It does not alter the original workbook and can be invoked anywhere there is
a break in the code execution.

"Neptune Dinosaur" wrote:

G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break. VBA
help does not return any remotely relevant hits when I type in keywords like
"Enable" or "CancelKey" or anything relevant to the issue. Where should I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

In VBA Help, type in EnableCancelKey.
You can also find it as a property of the Application object.

Escape/ControlBreak generates Err.Number 18.
You can trap for that in your error handler if you set EnableCancelKey to xlErrorHandler.
It is then up to you to write whatever code you need when that error occurs...
'--
Exit Sub
ErrorHandler:
If Err.Number < 18 then
'Do something to handle error
Else
'Do something to make your impatient user happy
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break. VBA
help does not return any remotely relevant hits when I type in keywords like
"Enable" or "CancelKey" or anything relevant to the issue. Where should I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

Well, there must to be something deficient about our implementation of Excel
and/or VBA. We have Excel 2003 (11.8211.8202) SP3 and VBA 6.5 (Retail
6.5.1024). I get "no results found" when I enter "EnableCancelKey" in VBA
Help (it is very common to get no relevant results when you type in the exact
words that ought to get a hit). And no matter whether I have EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro. The best I
get is the uncontrolled RTE-like box that only allows "Debug" or "End" (which
is the very thing I am trying to avoid as it will be un-savvy users who will
be seeing it).

Do I need to change the change the error-trapping option to "Break On All
Errors"? At present it is on "Break on Unhandled errors", which traps
everything else I have ever needed to trap via the On Error GoTo caper.

I'm looking for a practical solution to this rather than a "this should
work" one, or at least a clue as to whether I might have an environment in
which this cannot be done.
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:

In VBA Help, type in EnableCancelKey.
You can also find it as a property of the Application object.

Escape/ControlBreak generates Err.Number 18.
You can trap for that in your error handler if you set EnableCancelKey to xlErrorHandler.
It is then up to you to write whatever code you need when that error occurs...
'--
Exit Sub
ErrorHandler:
If Err.Number < 18 then
'Do something to handle error
Else
'Do something to make your impatient user happy
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break. VBA
help does not return any remotely relevant hits when I type in keywords like
"Enable" or "CancelKey" or anything relevant to the issue. Where should I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

I have the same version of XL you do and EnableCancelKey can be found
in the VBA help file. However, I have recently reinstalled the help files and
they are a little more reliable now (no blank white sheets). In any case,
as I said in my last post, you set it to xlErrorHandler not True or False...
'--
Sub ThisRunsToTheEnd
On Error GoTo StopTheMovie
Application.EnableCancelKey = xlErrorHandler '=2

'Important code that bores the user when run.
'Put DoEvents inside time consuming loops

Exit Sub
StopTheMovie:
If Err.Number < 18 then
'Do something to handle error
Else
MsgBox "Exiting Video",vbExclamation, "You Won't Learn Anything"
End If
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Well, there must to be something deficient about our implementation of Excel
and/or VBA. We have Excel 2003 (11.8211.8202) SP3 and VBA 6.5 (Retail
6.5.1024). I get "no results found" when I enter "EnableCancelKey" in VBA
Help (it is very common to get no relevant results when you type in the exact
words that ought to get a hit). And no matter whether I have EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro. The best I
get is the uncontrolled RTE-like box that only allows "Debug" or "End" (which
is the very thing I am trying to avoid as it will be un-savvy users who will
be seeing it).

Do I need to change the change the error-trapping option to "Break On All
Errors"? At present it is on "Break on Unhandled errors", which traps
everything else I have ever needed to trap via the On Error GoTo caper.

I'm looking for a practical solution to this rather than a "this should
work" one, or at least a clue as to whether I might have an environment in
which this cannot be done.
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:

In VBA Help, type in EnableCancelKey.
You can also find it as a property of the Application object.

Escape/ControlBreak generates Err.Number 18.
You can trap for that in your error handler if you set EnableCancelKey to xlErrorHandler.
It is then up to you to write whatever code you need when that error occurs...
'--
Exit Sub
ErrorHandler:
If Err.Number < 18 then
'Do something to handle error
Else
'Do something to make your impatient user happy
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break. VBA
help does not return any remotely relevant hits when I type in keywords like
"Enable" or "CancelKey" or anything relevant to the issue. Where should I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

And no matter whether I have EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro.

Maybe I'm reading too literally, but True and False are not the values you
want, xlErrorHandler is.

xlErrorHandler is a constant of 2, which is not achievable using True (-1)
or False (0)

From the Object Browser in the VBE, EnableCancelKey:

Const xlDisabled = 0
Const xlInterrupt = 1
Const xlErrorHandler = 2


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Neptune Dinosaur" wrote in message
...
Well, there must to be something deficient about our implementation of
Excel
and/or VBA. We have Excel 2003 (11.8211.8202) SP3 and VBA 6.5 (Retail
6.5.1024). I get "no results found" when I enter "EnableCancelKey" in
VBA
Help (it is very common to get no relevant results when you type in the
exact
words that ought to get a hit). And no matter whether I have
EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro. The best
I
get is the uncontrolled RTE-like box that only allows "Debug" or "End"
(which
is the very thing I am trying to avoid as it will be un-savvy users who
will
be seeing it).

Do I need to change the change the error-trapping option to "Break On All
Errors"? At present it is on "Break on Unhandled errors", which traps
everything else I have ever needed to trap via the On Error GoTo caper.

I'm looking for a practical solution to this rather than a "this should
work" one, or at least a clue as to whether I might have an environment in
which this cannot be done.
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:

In VBA Help, type in EnableCancelKey.
You can also find it as a property of the Application object.

Escape/ControlBreak generates Err.Number 18.
You can trap for that in your error handler if you set EnableCancelKey to
xlErrorHandler.
It is then up to you to write whatever code you need when that error
occurs...
'--
Exit Sub
ErrorHandler:
If Err.Number < 18 then
'Do something to handle error
Else
'Do something to make your impatient user happy
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break. VBA
help does not return any remotely relevant hits when I type in keywords
like
"Enable" or "CancelKey" or anything relevant to the issue. Where should
I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it,
so I
promise I am not just being lazy in calling for help. I need to be
able to
trap the Control-Break "event" when the user hits those keys to
interrupt a
macro, in the same way (if possible) that you trap an error. I have a
data
input/output application in Excel 2003/VBA that includes a "live demo"
of how
to use the file. It runs for 2 - 3 minutes or even longer, depending
on how
long the user lingers over the message boxes. I want the user to be
able to
abort the demo at any time by hitting Ctrl-Brk without getting the
scary run
time error that you normally get when you do that, i.e. I would like
this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide
Yes/No
message boxes at several strategic points throughout the demo, but it
would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

To add, you should see a list of valid constants pop up after you type
Application.EnableCancelKey =

If you don't see it, my guess is that the VBA project has a non-obvious
compilation error somewhere (assuming you don't have Auto List Members
unchecked).
Add Option Explicit at the top of all code modules and then Debug - Compile
and fix them. It's a good idea to check "Require Variable Declaration" from
Tools - Options -Editor so that Excel can add Option Explicit
automatically before any code is written.

--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
And no matter whether I have EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro.

Maybe I'm reading too literally, but True and False are not the values you
want, xlErrorHandler is.

xlErrorHandler is a constant of 2, which is not achievable using True (-1)
or False (0)

From the Object Browser in the VBE, EnableCancelKey:

Const xlDisabled = 0
Const xlInterrupt = 1
Const xlErrorHandler = 2


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Neptune Dinosaur" wrote in message
...
Well, there must to be something deficient about our implementation of
Excel
and/or VBA. We have Excel 2003 (11.8211.8202) SP3 and VBA 6.5 (Retail
6.5.1024). I get "no results found" when I enter "EnableCancelKey" in
VBA
Help (it is very common to get no relevant results when you type in the
exact
words that ought to get a hit). And no matter whether I have
EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro. The best
I
get is the uncontrolled RTE-like box that only allows "Debug" or "End"
(which
is the very thing I am trying to avoid as it will be un-savvy users who
will
be seeing it).

Do I need to change the change the error-trapping option to "Break On All
Errors"? At present it is on "Break on Unhandled errors", which traps
everything else I have ever needed to trap via the On Error GoTo caper.

I'm looking for a practical solution to this rather than a "this should
work" one, or at least a clue as to whether I might have an environment
in
which this cannot be done.
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:

In VBA Help, type in EnableCancelKey.
You can also find it as a property of the Application object.

Escape/ControlBreak generates Err.Number 18.
You can trap for that in your error handler if you set EnableCancelKey
to xlErrorHandler.
It is then up to you to write whatever code you need when that error
occurs...
'--
Exit Sub
ErrorHandler:
If Err.Number < 18 then
'Do something to handle error
Else
'Do something to make your impatient user happy
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break.
VBA
help does not return any remotely relevant hits when I type in keywords
like
"Enable" or "CancelKey" or anything relevant to the issue. Where should
I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it,
so I
promise I am not just being lazy in calling for help. I need to be
able to
trap the Control-Break "event" when the user hits those keys to
interrupt a
macro, in the same way (if possible) that you trap an error. I have a
data
input/output application in Excel 2003/VBA that includes a "live demo"
of how
to use the file. It runs for 2 - 3 minutes or even longer, depending
on how
long the user lingers over the message boxes. I want the user to be
able to
abort the demo at any time by hitting Ctrl-Brk without getting the
scary run
time error that you normally get when you do that, i.e. I would like
this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide
Yes/No
message boxes at several strategic points throughout the demo, but it
would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at
once

Jim et al

Thanks for that. Sorry I got a bit narky, but this was actually the first
time I ever got badly stumped by an Excel/VBA solution. I work in a very
large Govt organisation in Australia that has its reasons for de-implementing
(or not implementing) certain bits of the Office suite and VBA. I've always
suspected that there was somethiong incomplete about the VBA Help and maybe
the Object browser and some other bits; now I know ....

I now have a fully functioning neat exit from my magnificent tutorial, so
that people are able to leave their desks in a hurry and go out into the
street to vomit if they need to, so thanks again to all who pitched in.

One more thing - I presume that after the demo (or after the early exit) I
need to reset the CancelKey as follows :
Application.EnableCancelKey = xlInterrupt ?????
Is this necessary, or does VBA know to default back to this by itself?

My next Excel/VBA production will be a true Hollywood blockbuster : "Indiana
Jones Hates His Own Sequel". Free tickets to all who helped me get my start
......

--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


I have the same version of XL you do and EnableCancelKey can be found
in the VBA help file. However, I have recently reinstalled the help files and
they are a little more reliable now (no blank white sheets). In any case,
as I said in my last post, you set it to xlErrorHandler not True or False...
'--
Sub ThisRunsToTheEnd
On Error GoTo StopTheMovie
Application.EnableCancelKey = xlErrorHandler '=2

'Important code that bores the user when run.
'Put DoEvents inside time consuming loops

Exit Sub
StopTheMovie:
If Err.Number < 18 then
'Do something to handle error
Else
MsgBox "Exiting Video",vbExclamation, "You Won't Learn Anything"
End If
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Well, there must to be something deficient about our implementation of Excel
and/or VBA. We have Excel 2003 (11.8211.8202) SP3 and VBA 6.5 (Retail
6.5.1024). I get "no results found" when I enter "EnableCancelKey" in VBA
Help (it is very common to get no relevant results when you type in the exact
words that ought to get a hit). And no matter whether I have EnableCancelKey
set to True or False, I do not get a trappable error when I hit Escape or
Escape/Break or Control/Break during the currency of the macro. The best I
get is the uncontrolled RTE-like box that only allows "Debug" or "End" (which
is the very thing I am trying to avoid as it will be un-savvy users who will
be seeing it).

Do I need to change the change the error-trapping option to "Break On All
Errors"? At present it is on "Break on Unhandled errors", which traps
everything else I have ever needed to trap via the On Error GoTo caper.

I'm looking for a practical solution to this rather than a "this should
work" one, or at least a clue as to whether I might have an environment in
which this cannot be done.
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:

In VBA Help, type in EnableCancelKey.
You can also find it as a property of the Application object.

Escape/ControlBreak generates Err.Number 18.
You can trap for that in your error handler if you set EnableCancelKey to xlErrorHandler.
It is then up to you to write whatever code you need when that error occurs...
'--
Exit Sub
ErrorHandler:
If Err.Number < 18 then
'Do something to handle error
Else
'Do something to make your impatient user happy
End If
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Neptune Dinosaur"
wrote in message
Hmmmm

Application.EnableCancelKey turns OFF the response to Control-Break. VBA
help does not return any remotely relevant hits when I type in keywords like
"Enable" or "CancelKey" or anything relevant to the issue. Where should I be
looking?
--
Time is just the thing that keeps everything from happening all at once


"Jim Cone" wrote:


Application.EnableCancelKey is what you are looking for.
Check the help file for details.
If you run any lengthy loops, make sure to put a DoEvents function
in there someplace.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Neptune Dinosaur"

wrote in message
G'day macrosaurs ....

I've had a good look around for the answer to this and can't find it, so I
promise I am not just being lazy in calling for help. I need to be able to
trap the Control-Break "event" when the user hits those keys to interrupt a
macro, in the same way (if possible) that you trap an error. I have a data
input/output application in Excel 2003/VBA that includes a "live demo" of how
to use the file. It runs for 2 - 3 minutes or even longer, depending on how
long the user lingers over the message boxes. I want the user to be able to
abort the demo at any time by hitting Ctrl-Brk without getting the scary run
time error that you normally get when you do that, i.e. I would like this to
be a neat, controlled exit (my users are not hightly IT-savvy).

An alternative that I was considering would be to actually provide Yes/No
message boxes at several strategic points throughout the demo, but it would
be nice to have the "leave at any time" option if possible.

Any ideas?
--
Time is just the thing that keeps everything from happening all at once

Glad you got it working. Can't wait to see that next feature.<g
Following is from the xl97 help file for EnableCancelKey...

----
"Controls how Microsoft Excel handles CTRL+BREAK
(or ESC or COMMAND+PERIOD) user interruptions to the running procedure.
Read/write Long.

Can be one of the following XlEnableCancelKey constants.
xlDisabled Cancel key trapping is completely disabled.

xlInterrupt The current procedure is interrupted,
and the user can debug or end the procedure.

xlErrorHandler The interrupt is sent to the running procedure as an error,
trappable by an error handler set up with an On Error GoTo statement.
The trappable error code is 18.
Remarks
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.

The EnableCancelKey property is always reset to xlInterrupt
whenever Microsoft Excel returns to the idle state and there's no code running.

To trap or disable cancellation in your procedure, you must explicitly change
the EnableCancelKey property every time the procedure is called."
----

Also...
Maybe your tech people just don't fully install the VBA help files and the
file are just waiting for their call to duty.
If so, another post of mine... http://tinyurl.com/6exe4z
'--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)






"Neptune Dinosaur"
wrote in message
Jim et al
Thanks for that. Sorry I got a bit narky, but this was actually the first
time I ever got badly stumped by an Excel/VBA solution. I work in a very
large Govt organisation in Australia that has its reasons for de-implementing
(or not implementing) certain bits of the Office suite and VBA. I've always
suspected that there was somethiong incomplete about the VBA Help and maybe
the Object browser and some other bits; now I know ....

I now have a fully functioning neat exit from my magnificent tutorial, so
that people are able to leave their desks in a hurry and go out into the
street to vomit if they need to, so thanks again to all who pitched in.

One more thing - I presume that after the demo (or after the early exit) I
need to reset the CancelKey as follows :
Application.EnableCancelKey = xlInterrupt ?????
Is this necessary, or does VBA know to default back to this by itself?

My next Excel/VBA production will be a true Hollywood blockbuster : "Indiana
Jones Hates His Own Sequel". Free tickets to all who helped me get my start
--
Time is just the thing that keeps everything from happening all at once