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

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

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
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=477826

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

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 procedu

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"
wrote in
message
...

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