How to stop VBA code running?

Question

Hi, guys.

Say I have a button embedded into my spreadsheet that launches some VBA function.

Private Sub CommandButton1_Click()
    SomeVBASub
End Sub

Private Sub SomeVBASub
    DoStuff
    DoAnotherStuff
    AndFinallyDothis
End Sub

I'd like to have an opportunity to have some sort of a "cancel" button that would stop SomeVBASub execution at an arbitrary moment, and I'm not into involving Ctrl+Break here, 'cause I'd like to do it silently.

I guess this should be quite common issue, any ideas?

Thanks.

Answer 1

Add another button called "CancelButton" that sets a flag, and then check for that flag.

If you have long loops in the "stuff" then check for it there too and exit if it's set. Use DoEvents inside long loops to ensure that the UI works.

Bool Cancel
Private Sub CancelButton_OnClick()
    Cancel=True
End Sub
...
Private Sub SomeVBASub
    Cancel=False
    DoStuff
    If Cancel Then Exit Sub
    DoAnotherStuff
    If Cancel Then Exit Sub
    AndFinallyDothis
End Sub

Answer 2

what jamietre said, but

Private Sub SomeVBASub
    Cancel=False
    DoStuff
    If not Cancel Then DoAnotherStuff
    If not Cancel Then AndFinallyDothis
End Sub

Answer 3

How about Application.EnableCancelKey - Use the Esc button

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!)
    ' do something here
Next x

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

Snippet from http://msdn.microsoft.com/en-us/library/aa214566(office.11).aspx