How to cancel midway?

  • Thread starter Thread starter Sing
  • Start date Start date
S

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.
 
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)
 
Back
Top