Sub test()
    On Error GoTo handleCancel
    Application.EnableCancelKey = xlErrorHandler
    MsgBox "This may take a long time: press ESC to cancel"
    Do
        ' your Stuf here
        DoEvents
    Loop
handleCancel:
    If Err = 18 Then
        MsgBox "You cancelled"
    End If
End Sub


Function allsheets() As String
Dim x As Integer
Dim i As Integer
    On Error GoTo handleCancel
    Application.EnableCancelKey = xlErrorHandler
    MsgBox "This may take a long time: press ESC to cancel"
    For x = 1 To 1000000    ' Do something 1,000,000 times (long!)
        i = i + 1
        Debug.Print i
    Next x

handleCancel:
    If Err = 18 Then
        MsgBox "You cancelled"
    End If
End Function




Ignore Error

Sub Macro1()
    On Error Resume Next
End Sub