469,167 Members | 1,161 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

VB & Excel - how to stop a function

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
Jul 17 '05 #1
1 15314

"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


Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Mike | last post: by
1 post views Thread by u7djo | last post: by
reply views Thread by Bob | last post: by
1 post views Thread by Ray Ackley | last post: by
6 posts views Thread by Mark Rae | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.