Difference Escape and Ctrl + Break to interrupt code

Have a bit of code with this simplified construction:


Sub Test()

On Error GoTo STOPTEST
Application.EnableCancelKey = xlErrorHandler

OtherSub

Do While Condition = True
DoEvents
OtherSub
DoEvents
Loop

STOPTEST:

MsgBox "Stopped Test"

End Sub

Now when I am in the Sub OtherSub the Escape key will interrupt this code
and go to STOPTEST, but not when
I am in the Do While loop and not in OtherSub.
Ctrl + Break/Pause will work in both situations.
What could be the explanation for this?
Ideally I would like the Escape key to work in both situations.


RBS

Hi Bart,

I think it's related to the DoEvents.

Also note that if you have an errorhandler in Othersub
THAT will take over.

e.g.

Sub Test()
On Error GoTo errProc:
Application.EnableCancelKey = xlErrorHandler

BusyBee
MsgBox "Returned/completed"
Exit Sub

errProc:
MsgBox "Stopped Test"
End Sub

Sub BusyBee()
Dim i#, n#, m#
m = 100000000#

On Error GoTo errProc:
'note when called from test the key is in effect

For i = 1 To m: n = n + 1: Next

MsgBox "switch handler"
On Error GoTo 0
'each error will now be handled by the handler from the caller.
'but the enablekey is still active..
For i = 1 To m: n = n + 1: Next

MsgBox "from now on Esc wont work"
For i = 1 To m
n = n + 1: If n Mod 1000 Then DoEvents
Next

Exit Sub

errProc:
If MsgBox("Stopped Other... Continue?", vbYesNo) = vbYes Then
Resume
Else
End
End If
End Sub



hth
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


RB Smissaert wrote :

> Have a bit of code with this simplified construction:
>
>
> Sub Test()
>
> On Error GoTo STOPTEST
> Application.EnableCancelKey = xlErrorHandler
>
> OtherSub
>
> Do While Condition = True
> DoEvents
> OtherSub
> DoEvents
> Loop
>
> STOPTEST:
>
> MsgBox "Stopped Test"
>
> End Sub
>
> Now when I am in the Sub OtherSub the Escape key will interrupt this
> code and go to STOPTEST, but not when I am in the Do While loop and
> not in OtherSub. Ctrl + Break/Pause will work in both situations.
> What could be the explanation for this?
> Ideally I would like the Escape key to work in both situations.
>
>
> RBS

Hi keepITcool,

I still don't quite get it.
The Sub OtherSub has no error handler. Leaving out one or both of the
DoEvents statements
makes matters worse as Excel will just freeze or crash on pressing the
Escape key, so I need these.
I have done a bit more reading about this and it seems that Ctrl +
Pause/Break is just more reliable than Escape.
I am in Excel 2002. Maybe I need some API to capture a keypress and take
action according.

RBS


"keepITcool" <xrrcvgpbby@puryyb.ay> wrote in message
news:xn0e1e8j35o1vet002keepitcoolnl@msnews.microsoft.com...
> Hi Bart,
>
> I think it's related to the DoEvents.
>
> Also note that if you have an errorhandler in Othersub
> THAT will take over.
>
> e.g.
>
> Sub Test()
> On Error GoTo errProc:
> Application.EnableCancelKey = xlErrorHandler
>
> BusyBee
> MsgBox "Returned/completed"
> Exit Sub
>
> errProc:
> MsgBox "Stopped Test"
> End Sub
>
> Sub BusyBee()
> Dim i#, n#, m#
> m = 100000000#
>
> On Error GoTo errProc:
> 'note when called from test the key is in effect
>
> For i = 1 To m: n = n + 1: Next
>
> MsgBox "switch handler"
> On Error GoTo 0
> 'each error will now be handled by the handler from the caller.
> 'but the enablekey is still active..
> For i = 1 To m: n = n + 1: Next
>
> MsgBox "from now on Esc wont work"
> For i = 1 To m
> n = n + 1: If n Mod 1000 Then DoEvents
> Next
>
> Exit Sub
>
> errProc:
> If MsgBox("Stopped Other... Continue?", vbYesNo) = vbYes Then
> Resume
> Else
> End
> End If
> End Sub
>
>
>
> hth
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> RB Smissaert wrote :
>
>> Have a bit of code with this simplified construction:
>>
>>
>> Sub Test()
>>
>> On Error GoTo STOPTEST
>> Application.EnableCancelKey = xlErrorHandler
>>
>> OtherSub
>>
>> Do While Condition = True
>> DoEvents
>> OtherSub
>> DoEvents
>> Loop
>>
>> STOPTEST:
>>
>> MsgBox "Stopped Test"
>>
>> End Sub
>>
>> Now when I am in the Sub OtherSub the Escape key will interrupt this
>> code and go to STOPTEST, but not when I am in the Do While loop and
>> not in OtherSub. Ctrl + Break/Pause will work in both situations.
>> What could be the explanation for this?
>> Ideally I would like the Escape key to work in both situations.
>>
>>
>> RBS

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
> I have done a bit more reading about this and it seems that Ctrl +
> Pause/Break is just more reliable than Escape.

You have hit the nail on the head, RBS. This has been my personal
experience.

> I am in Excel 2002. Maybe I need some API to capture a keypress and take
> action according.

Perhaps you are looking for something like this:

http://groups-beta.google.com/group/...97f656cd?hl=en

Regards,

Vasant

Vasant,

Thanks, that looks like a nice and simple API and will give that a try.

RBS

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
>> I have done a bit more reading about this and it seems that Ctrl +
>> Pause/Break is just more reliable than Escape.
>
> You have hit the nail on the head, RBS. This has been my personal
> experience.
>
>> I am in Excel 2002. Maybe I need some API to capture a keypress and take
>> action according.
>
> Perhaps you are looking for something like this:
>
> http://groups-beta.google.com/group/...97f656cd?hl=en
>
> Regards,
>
> Vasant
>
>

Vasant,

Tried that API and it works perfect.
Thanks again for the tip.

RBS


"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
>> I have done a bit more reading about this and it seems that Ctrl +
>> Pause/Break is just more reliable than Escape.
>
> You have hit the nail on the head, RBS. This has been my personal
> experience.
>
>> I am in Excel 2002. Maybe I need some API to capture a keypress and take
>> action according.
>
> Perhaps you are looking for something like this:
>
> http://groups-beta.google.com/group/...97f656cd?hl=en
>
> Regards,
>
> Vasant
>
>

Glad to help, RBS!

--

Vasant

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:u25HZfSSFHA.3704@TK2MSFTNGP12.phx.gbl...
> Vasant,
>
> Tried that API and it works perfect.
> Thanks again for the tip.
>
> RBS
>
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> > news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
> >> I have done a bit more reading about this and it seems that Ctrl +
> >> Pause/Break is just more reliable than Escape.
> >
> > You have hit the nail on the head, RBS. This has been my personal
> > experience.
> >
> >> I am in Excel 2002. Maybe I need some API to capture a keypress and
take
> >> action according.
> >
> > Perhaps you are looking for something like this:
> >
> >
http://groups-beta.google.com/group/...97f656cd?hl=en
> >
> > Regards,
> >
> > Vasant
> >
> >
>

Vasant,

One problem with this API is that it can also pick up an Escape press from
outside Excel.
For now I have gone back to my old way of doing this. Will look for another
API.

RBS


"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:udt0$qSSFHA.3336@TK2MSFTNGP10.phx.gbl...
> Glad to help, RBS!
>
> --
>
> Vasant
>
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:u25HZfSSFHA.3704@TK2MSFTNGP12.phx.gbl...
>> Vasant,
>>
>> Tried that API and it works perfect.
>> Thanks again for the tip.
>>
>> RBS
>>
>>
>> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
>> news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
>> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
>> > news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
>> >> I have done a bit more reading about this and it seems that Ctrl +
>> >> Pause/Break is just more reliable than Escape.
>> >
>> > You have hit the nail on the head, RBS. This has been my personal
>> > experience.
>> >
>> >> I am in Excel 2002. Maybe I need some API to capture a keypress and
> take
>> >> action according.
>> >
>> > Perhaps you are looking for something like this:
>> >
>> >
> http://groups-beta.google.com/group/...97f656cd?hl=en
>> >
>> > Regards,
>> >
>> > Vasant
>> >
>> >
>>
>
>

Hi RBS:

Haven't thought this through, but perhaps there is a way of checking if
Excel is the active app at the time the key is pressed? Here's a
possibility:

http://groups-beta.google.com/group/...e0ab6fda?hl=en

Regards,

Vasant


"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:uuLVsyZSFHA.904@tk2msftngp13.phx.gbl...
> Vasant,
>
> One problem with this API is that it can also pick up an Escape press from
> outside Excel.
> For now I have gone back to my old way of doing this. Will look for
another
> API.
>
> RBS
>
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:udt0$qSSFHA.3336@TK2MSFTNGP10.phx.gbl...
> > Glad to help, RBS!
> >
> > --
> >
> > Vasant
> >
> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> > news:u25HZfSSFHA.3704@TK2MSFTNGP12.phx.gbl...
> >> Vasant,
> >>
> >> Tried that API and it works perfect.
> >> Thanks again for the tip.
> >>
> >> RBS
> >>
> >>
> >> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> >> news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> >> > news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
> >> >> I have done a bit more reading about this and it seems that Ctrl +
> >> >> Pause/Break is just more reliable than Escape.
> >> >
> >> > You have hit the nail on the head, RBS. This has been my personal
> >> > experience.
> >> >
> >> >> I am in Excel 2002. Maybe I need some API to capture a keypress and
> > take
> >> >> action according.
> >> >
> >> > Perhaps you are looking for something like this:
> >> >
> >> >
> >
http://groups-beta.google.com/group/...97f656cd?hl=en
> >> >
> >> > Regards,
> >> >
> >> > Vasant
> >> >
> >> >
> >>
> >
> >
>

...
Bart,
aha.. but if tou raise error 18.. it will get caught.
and if your caller sub will handle the error...

Option Explicit
Private Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub escTest()
On Error GoTo errH:
Application.EnableCancelKey = xlErrorHandler

Call BusyBee
MsgBox "done"
Exit Sub

errH:
MsgBox Err.Number & vbLf & Err.Description
End Sub

Sub BusyBee()
Dim i&, n&, m&
m = 100000000
For i = 1 To m
If GetKeyState(vbKeyEscape) < 0 Then Err.Raise 18
n = n + 1
Next
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


RB Smissaert wrote :

> Vasant,
>
> One problem with this API is that it can also pick up an Escape press
> from outside Excel. For now I have gone back to my old way of doing
> this. Will look for another API.
>
> RBS
>
>
> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> news:udt0$qSSFHA.3336@TK2MSFTNGP10.phx.gbl...
> > Glad to help, RBS!
> >
> > --
> >
> > Vasant
> >
> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> > news:u25HZfSSFHA.3704@TK2MSFTNGP12.phx.gbl...
> >> Vasant,
> > >
> >> Tried that API and it works perfect.
> >> Thanks again for the tip.
> > >
> >> RBS
> > >
> > >
> >> "Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
> >> news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
> >> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> >> > news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
> >> >> I have done a bit more reading about this and it seems that
> Ctrl + >> >> Pause/Break is just more reliable than Escape.
> >> >
> >> > You have hit the nail on the head, RBS. This has been my personal
> >> > experience.
> >> >
> >> >> I am in Excel 2002. Maybe I need some API to capture a keypress
> and
> > take
> >> >> action according.
> >> >
> >> > Perhaps you are looking for something like this:
> >> >
> >> >
> > http://groups-beta.google.com/group/...xcel.programmi
> > ng/msg/5ca8f27597f656cd?hl=en
> >> >
> >> > Regards,
> >> >
> >> > Vasant
> >> >
> >> >
> > >
> >
> >

I think in my particular situation Ctrl + Pause/Break is actually fine,
because the Sub OtherSub will
run about every 20 seconds and in between you can do some things in Excel.
So you don't want to lose the Escape key as this can be needed for example
to get dialogs away.
It also will keep the code simple. Will have a look at both other
suggestions though.

RBS


"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:uuLVsyZSFHA.904@tk2msftngp13.phx.gbl...
> Vasant,
>
> One problem with this API is that it can also pick up an Escape press from
> outside Excel.
> For now I have gone back to my old way of doing this. Will look for
> another API.
>
> RBS
>
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:udt0$qSSFHA.3336@TK2MSFTNGP10.phx.gbl...
>> Glad to help, RBS!
>>
>> --
>>
>> Vasant
>>
>> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
>> news:u25HZfSSFHA.3704@TK2MSFTNGP12.phx.gbl...
>>> Vasant,
>>>
>>> Tried that API and it works perfect.
>>> Thanks again for the tip.
>>>
>>> RBS
>>>
>>>
>>> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
>>> news:OHsqSVSSFHA.1564@TK2MSFTNGP09.phx.gbl...
>>> > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
>>> > news:%234NEAhRSFHA.1476@TK2MSFTNGP09.phx.gbl...
>>> >> I have done a bit more reading about this and it seems that Ctrl +
>>> >> Pause/Break is just more reliable than Escape.
>>> >
>>> > You have hit the nail on the head, RBS. This has been my personal
>>> > experience.
>>> >
>>> >> I am in Excel 2002. Maybe I need some API to capture a keypress and
>> take
>>> >> action according.
>>> >
>>> > Perhaps you are looking for something like this:
>>> >
>>> >
>> http://groups-beta.google.com/group/...97f656cd?hl=en
>>> >
>>> > Regards,
>>> >
>>> > Vasant
>>> >
>>> >
>>>
>>
>>
>