How to cancel midway?

[Sing]

Dear Excel Gurus,

I have VBA code that takes about 5 minutes to run. Sometimes, I want to
cancel it halfway.

Can I add a new "Cancel" button to cancel the operation? What code should I
put for that button?

Thanks for your advice.

 

[Chip Pearson]

The simplest way is to set the Application.EnableCancelKey property to
xlErrorHandler. This will raise a run time error when the user presses CTRL
BREAK. For example,

Sub AAA()

Application.EnableCancelKey = xlErrorHandler
On Error GoTo ErrH:
'
' your code here
'
Exit Sub
ErrH:
If Err.Number = 18 Then
MsgBox "You pressed CTRL+BREAK"
Else
' other error handling
End If

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)