VB & Excel - how to stop a function

[Jakub]

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

[Steve Gerrard]

Re: VB & Excel - how to stop a function


"Jakub" <jzwierz@yahoo. co.uk> wrote in message
news:f3606175.0 503250212.c1be5 72@posting.goog le.com...[color=blue]
>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[/color]

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