"Jakub" <jz*****@yahoo.co.uk> wrote in message
news:f3*************************@posting.google.co m...
I have the following problem. Suppose I added a button to my worksheet
and when user clicks the button a function starts. Now, as long as
this function is running the worksheet is not active. The question is:
is it possible to stop this function from the worksheet, for example
is it possible to add another button to the worksheet so that the user
could click this button and stop the function that is running after he
clicked the first button?
rgds.
Jakub
You need to do two things in the function do allow this to happen.
One is to include DoEvents in the loop, so that a second button click has a
chance to register.
The other is to put a check in the loop, so that if a certain variable is set,
the loop stops. You set this variable false when the first button is clicked,
and true when the second button is clicked.
Here is an example. Put the code in a module, then assign StartIt to one button,
and StopIt to another.
Dim Flag As Boolean
Public Sub StartIt()
Flag = False
Call RunLoop
End Sub
Public Sub StopIt()
Flag = True
End Sub
Sub RunLoop()
Dim x As Double
Dim n As Long
Dim nCnt As Long
Do
x = 32.4
For n = 1 To 1000
' act busy
x = Sqr(x)
Next n
nCnt = nCnt + 1
DoEvents
Loop Until Flag = True
MsgBox nCnt & " loops."
End Sub