Stop Macro button in Excel VB

[grahammal]

I have many buttons on my spreadsheets that run macros. Some of these macros are searching through large data bases. As the results of these searches are displayed in my worksheet, sometimes I can see the the information that I was searching for and would then like to stop the macro from running rather than searching through the rest of the database. I would like to be able to stop the macro from running by clicking on a professional looking stop button rather than pressing Ctrl + Break.
Is there any VB script that can be programmed into a standard button that will halt the macro?

[K Dales]

It depends on the details of your code, but there are ways to do this.
Consider the following simple example:

Public StopNow As Boolean

Sub Button1_Click()
StopNow = False
For i = 1 To 30000
Range("A1").Value = i
' DoEvents
If StopNow Then Exit For
Next i
End Sub

Sub Button2_Click()
StopNow = True
End Sub

Key points:
- A variable that can be shared between the two button click events, or
whatever subs there are that drive your code.
- A DoEvents statement in the loop of the main code - otherwise it will be
impossible to click the 2nd button
- Clicking the 2nd button sets the shared variable to a specific value to
flag that you wish to stop the processing
- The main code checks the value of the variable as it loops and if it sees
the flag is set it exits the loop

In more complex situations you may have many places where you need to put
the DoEvents and check the value of the "flag" variable - also, you may need
to add some code that "cleans up" any leftover work to be done rather than
just bring your code to a sudden stop.
--
- K Dales


"grahammal" wrote:

>
> I have many buttons on my spreadsheets that run macros. Some of these
> macros are searching through large data bases. As the results of these
> searches are displayed in my worksheet, sometimes I can see the the
> information that I was searching for and would then like to stop the
> macro from running rather than searching through the rest of the
> database. I would like to be able to stop the macro from running by
> clicking on a professional looking stop button rather than pressing
> Ctrl + Break.
> Is there any VB script that can be programmed into a standard button
> that will halt the macro?
>
>
> --
> grahammal
> ------------------------------------------------------------------------
> grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
> View this thread: http://www.excelforum.com/showthread...hreadid=477826
>
>

[grahammal]

Am trying to get to grips with your answer to my problem. For practise I have generated two buttons in a new empty spreadsheet and put your suggested code to the two buttons. When I run the first button I simply get my usual eggtimer cursor till it finishes. At what stage am I meant to get a chance to click on the second button.

[grahammal]

Sub Button1_Click()
StopNow = False
For i = 1 To 30000
Range("A1").Value = i
' DoEvents
If StopNow Then Exit For
Next i
End Sub


Sub Button2_Click()
StopNow = True
End Sub

I have tried the above idea to stop a macro continuing to run but it dos'nt work for me.
I generated the 2 buttons, copied the relevent code to the buttons and pressed the first button.
I can see the value clocking up in cell A1 but you can't press the second button to stop the macro from running???
Am I being stupid or have I missed something?

[Chip Pearson]

The problem is most likely that you have not declared the StopNow
variable. If you don't declare the variable, VBA will create it
within the procedure in which it finds it. Therefore, you have
two completely variables named StopNow, one in each procedure.

The solution is to declare a global variable above and outside of
any procedure:

Public StopNow As Boolean

If you had an "Option Explicit" statement at the top of the
module, as you should, the cause of the problem would be obvious
because you would get a "Variable Not Defined" compiler error.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"grahammal"
<[email protected]> wrote in
message
news:[email protected]...
>
> Sub Button1_Click()
> StopNow = False
> For i = 1 To 30000
> Range("A1").Value = i
> ' DoEvents
> If StopNow Then Exit For
> Next i
> End Sub
>
>
> Sub Button2_Click()
> StopNow = True
> End Sub
>
> I have tried the above idea to stop a macro continuing to run
> but it
> dos'nt work for me.
> I generated the 2 buttons, copied the relevent code to the
> buttons and
> pressed the first button.
> I can see the value clocking up in cell A1 but you can't press
> the
> second button to stop the macro from running???
> Am I being stupid or have I missed something?
>
>
> --
> grahammal
> ------------------------------------------------------------------------
> grahammal's Profile:
> http://www.excelforum.com/member.php...o&userid=20336
> View this thread:
> http://www.excelforum.com/showthread...hreadid=477826
>