Solved: Breaking out of Loop with Keyboard

[Opv]

I realize one can exit a loop using the Exit Loop statement when designated conditions are met. However, I'm curious as to whether there is a way to forcibly break out of a look by using the keyboard (and without having to confirm by clicking on a confirmation dialog box). I was hoping the ESC key would do the trick but no luck.

[Paul_Hossler]

The easiest way I know is to use

Application.EnableCancelKey = xlErrorHandler


Option Explicit
'pauses processing
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub BreakOut()
Dim i As Long
i = 1

On Error GoTo LoopEnd
'Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user
'interruptions to the running procedure
Application.EnableCancelKey = xlErrorHandler

Do While 1 = 1
Call Sleep(100)
DoEvents
i = i + 1
Application.StatusBar = i
Loop

Exit Sub
LoopEnd:
If Err = 18 Then
Application.StatusBar = False
Application.EnableCancelKey = xlInterrupt
MsgBox "All Done"
End If
End Sub


This will use the Control-Break key or the ESC key

There are some other ways to read the keypress and use the specific key to determine actions

Paul

[Opv]

Works like a charm.

Thanks!

Opv