Exiting a VBA Macro by user intervention
 

I have an issue where I am calling a control within an Excel spreadsheet.
The control basically fires off a command that runs X number of itterations
.. I want the user to be able to control the number of itterations because
the larger the number of itterations the more accurate is the answer. The
problem is if the user inputs a value too large for the number of
itterations the computer will chug away for hours and the only way to exit
the VB macro is hitting escape to stop it. My question is how can I put in
a control in the spreadsheet that will stop the macro during the execution
of it. I have tried several things and it apperas that while the
spreadsheet is under control of the macro the user has no ability to regain
control until the macro has finished, errors out, or the user hits esc to
break in.
I know I could limit the number of itterations by checking the input but I
really want the user to be able to let the program run for extended periods
of time. What would be best is if the user could click on a start control
and fire the macro, then click on a stop control to stop the macro and
display the results.

Exiting a VBA Macro by user intervention
 

JON

Put a module level variable in your module

Dim bStop as Boolean

If the control is on a worksheet, put it in the worksheet module. If on a
userform, the userform module. It goes before any procedures in the module.

Create a commandbutton to stop the macro with this code in its click event

Private Sub cmdStop_Click()
bStop = True
End Sub

Change cmdStop to whatever you name your control.

In the sub that loops, check the value of bStop and add a DoEvents

Private Sub cmdStart_Click()
....
For i = 1 to Max
DoEvents
If bStop Then
bStop = False
Exit Sub 'or other clean up activities that you may need
End If

'loop guts
Next i

End Sub

One caveat: If your looping sub is in a standard module and not in the same
module as your control event subs, then dimension bStop in the standard
module with the Public keyword

Public bStop As Boolean


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"JON" wrote in message
s.com...
I have an issue where I am calling a control within an Excel spreadsheet.
The control basically fires off a command that runs X number of
itterations
. I want the user to be able to control the number of itterations because
the larger the number of itterations the more accurate is the answer. The
problem is if the user inputs a value too large for the number of
itterations the computer will chug away for hours and the only way to exit
the VB macro is hitting escape to stop it. My question is how can I put
in
a control in the spreadsheet that will stop the macro during the execution
of it. I have tried several things and it apperas that while the
spreadsheet is under control of the macro the user has no ability to
regain
control until the macro has finished, errors out, or the user hits esc to
break in.
I know I could limit the number of itterations by checking the input but I
really want the user to be able to let the program run for extended
periods
of time. What would be best is if the user could click on a start control
and fire the macro, then click on a stop control to stop the macro and
display the results.