Stopping Code from Running whilst in Loop...

C

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
 
P

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top