Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control-Break
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
blank row after control break | Excel Discussion (Misc queries) | |||
Cannot Control - Break out of VBA | Excel Discussion (Misc queries) | |||
Control Break in Excel | Excel Programming | |||
Prevent control & break | Excel Programming | |||
Control & Break | Excel Programming |