Stopping Code from Running whilst in Loop...

[Chris Gorham]

Hi,

I'm performing a series of operations on cells within the used range of a
s/sheet. A summary of the code is as follows:

Dim c as range

on error goto errhandler
Application.EnableCancelKey = xlErrorHandler

For Each c In ActiveSheet.UsedRange

.....series of operations on each cell....

next c

exit sub

errhandler:

end sub

I'd like to give the user the option of breaking out of the loop using the
escape key. However the code stubbornly refuses to do this in practice...
I've tried using "doevents" without any luck - although I'm not sure that
I'm applying it correctly.

Suggestions....

Rgds....Chris

 

[Patrick Molloy]

I found that DOEVENTS causes odd behavious

my test procedure worked as expected.

However, I prefer to use a userform, with a cancel button. In your loop,
have a DOEVENTS, this will allow the PC system to trap the button click
event. Have the CANCEL button set a booloean, such as teh 'Quit' boolean
variable in my code. This always allows the the user to break cleanly from a
code loop, IMH Experience.


Sub test()
Dim x As Long
On Error GoTo trap
Dim QUIT As Boolean
Application.EnableCancelKey = xlErrorHandler
Do Until QUIT
For x = 1 To 100
Application.StatusBar = x
Next
Loop
Exit Sub
trap:
QUIT = True
Application.StatusBar = "break"

End Sub

 

[Kalpesh]

Hi,

This is the macro that I added to the workbook
-----------
Public Sub dolong()
On Error GoTo e
Application.EnableCancelKey = xlErrorHandler
While True
Debug.Print i
i = i + 1
Wend
Exit Sub

e:
If Err.Number = 18 Then
MsgBox "user stopped"
Stop
End If
End Sub
---------------------

Run this macro, using Tools ->Macro -> Macros-> Run (dolong)
Press ESC while it is running & you will see that the msgbox appears

HTH
Kalpesh