how to stop program with loop by click "Cancel" button

[Guest]

Hi, everyone
Right now I wanna do a time-comsuming loop in excel programming, for
example:

for i = 0 to 100000
for j = 0 to 10000
'my parts
doEevents
next
next

during run time, I still wanna use a "Cancel" button to let user have choice
to stop it during run time?
how to do it, Pls advice

 

[Lonnie M.]

Hi,
In your for 'j' loop you could show a user form at the start. Then
before the next statement test it for cancel value and then kill the
form. The form would load and unload for each time it loops, to the
user it would appear to be one continous form. If 'Cancel' is clicked
then you could Exit Sub or use a goto statement to exit the loop.
HTH--Lonnie M.

 

[Sharad]

At module level define a Public Variable useCanceled as Boolean.
(This should be outside any 'Sub' - 'End Sub'

In the commandbutton click event set this variable to true.
Private Sub CommandButton1_Click()
userCanceled = True
End Sub

Then modify your sub code as under. It also shows declaration of the
public variable:

Public userCanceled As Boolean

Sub test()
For i = 1 To 100000
If userCanceled Then
userCanceled = False
Exit For
End If
For j = 1 To 10000
'your code
DoEvents
If userCanceled Then Exit For
Next j
Next i
End Sub

I am assuming that your code is in a module. If not then \
'Public userCanceled As Boolean' should be moved to a module.

Sharad