How to stop VBA code running

Question

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

User · Answer

I do this a lot  A lot   -   I have got used to using  DoEvents  more often  but still tend to set things running without really double checking a sure stop method   Then  today  having done it again  I thought   Well just wait for the end in 3 hours   and started paddling around in the ribbon  Earlier  I had noticed in the  View  section of the Ribbon a  Macros  pull down  and thought I have a look to see if I could see my interminable Macro running      I now realise you can also get this up using Alt-F8   Then I thought  well what if I  Step into  a different Macro  would that rescue me  It did  -  It also works if you step into your running Macro  but you still lose where you re upto   unless you are a very lazy programmer like me and declare lots of  Global  variables  in which case the Global data is retained  -   K

User · Answer

This is an old post  but given the title of this question  the END option should be described in more detail  This can be used to stop ALL PROCEDURES  not just the subroutine running   It can also be used within a function to stop other Subroutines  which I find useful for some add-ins I work with    As Microsoft states       Terminates execution immediately  Never required by itself but may be placed anywhere in a procedure to end code execution  close files opened with the Open statement  and to clear variables   I noticed that the END method is not described in much detail  This can be used to stop ALL PROCEDURES  not just the subroutine running     Here is an illustrative example   Sub RunSomeMacros        Call FirstPart     Call SecondPart       the below code will not be executed if user clicks yes during SecondPart      Call ThirdPart     MsgBox  All of the macros have been run    End Sub  Private Sub FirstPart       MsgBox  This is the first macro   End Sub  Private Sub SecondPart       Dim answer As Long     answer   MsgBox  Do you want to stop the macros    vbYesNo       If answer   vbYes Then          Stops All macros          End     End If      MsgBox  You clicked   NO   so the macros are still rolling     End Sub  Private Sub ThirdPart       MsgBox  Final Macro was run   End Sub

User · Answer

what jamietre said  but  Private Sub SomeVBASub     Cancel False     DoStuff     If not Cancel Then DoAnotherStuff     If not Cancel Then AndFinallyDothis End Sub

User · Answer

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

User · Answer

For those using custom input box  Private Sub CommandButton1 Click    DoCmd Close acForm  Me Name End  End Sub

User · Answer

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

User · Answer

Or  if you want to avoid the use of a global variable you could use the rarely used  Tag property of the userform   Private Sub CommandButton1 Click       Me CommandButton1 Enabled   False  Disabling button so user cannot push it                                        multiple times     Me CommandButton1 caption    Wait      Jamie s suggestion     Me Tag    Cancel  End Sub  Private Sub SomeVBASub     If LCase UserForm1 Tag     cancel  Then         GoTo StopProcess     Else          DoStuff     End If  Exit Sub StopProcess       Here you can do some steps to be able to cancel process adequately      i e  setting collections to  Nothing  deleting some files    End Sub

[vba] How to stop VBA code running?

Examples related to vba

Examples related to excel