P
Paul S
Hi,
I want to brute force errors in a long loop with
On Error.Resume Next
But I also want to give user a chance to Ctrl Break and
clean up in an orderly way.
Variations on the following seem to work intermittently
but unreliably (.EnableCancelKey = xlErrorHandler inside
the loop slightly more reliable):
Sub ErrorTest()
With Application
For i = 1 To 100000
On Error GoTo ErrH
.EnableCancelKey = xlErrorHandler
'sometimes catches Ctrl Break here
On Error Resume Next
'don't want to break here
.EnableCancelKey = xlDisabled
x = 1 / 0 ' allow this error
Next
ErrH:
.EnableCancelKey = xlInterrupt
End With
MsgBox i & " Err " & Err.Number '18 = user Ctrl Break
End Sub
Not sure if what I'm trying to achieve is possible but any
solution appreciated.
TIA,
Paul
I want to brute force errors in a long loop with
On Error.Resume Next
But I also want to give user a chance to Ctrl Break and
clean up in an orderly way.
Variations on the following seem to work intermittently
but unreliably (.EnableCancelKey = xlErrorHandler inside
the loop slightly more reliable):
Sub ErrorTest()
With Application
For i = 1 To 100000
On Error GoTo ErrH
.EnableCancelKey = xlErrorHandler
'sometimes catches Ctrl Break here
On Error Resume Next
'don't want to break here
.EnableCancelKey = xlDisabled
x = 1 / 0 ' allow this error
Next
ErrH:
.EnableCancelKey = xlInterrupt
End With
MsgBox i & " Err " & Err.Number '18 = user Ctrl Break
End Sub
Not sure if what I'm trying to achieve is possible but any
solution appreciated.
TIA,
Paul