using a For-Next in a module and a Form

[Guest]

Hello,
I need same ideas because I do not know how to implement the "Start,
Continue, StartAllOver and Exit" buttons in the following Macro:

This is one module
sub checkCells()
dim errorFound as Boolean
for I = 1 to 500
....
....
errorFound = FindErrors()
if errorFound = True then
frmCheckCells.lblErrorMessage.Caption = "Error found for i = "& i
***PAUSE and WAIT for RESPONSE ***
end if
....
....
next I
end sub

And one form ( frmCheckCells ) with the following tools

4 buttons:
btnStartCheckingProcess, btnContinueCheckingProcess,
btnCancelCheckingProcess and btnStartAllOverCheckingProcess

And one Label (that will change for different kinds of error messages):
lblErrorMessage


What commands can I use to run this Macro correctly?
I would like to run it and then pause it when an error is found, prompt for
what kind of error message (in the form), then wait for the user to select
the desire button.

Also I want to implement this macro without using the "MsgBox commands"
prompts.

Thanks

Coco

 

[Bob Phillips]

Because you are dealing with objects on the form, you cannot pause like you
are suggesting. What you will need to do is to exit that routine, and then
react to the button click. Something like

sub checkCells()
dim errorFound as Boolean
for I = 1 to 500
....
....
errorFound = FindErrors()
if errorFound = True then
frmCheckCells.lblErrorMessage.Caption = "Error found for i = "& i
Exit Sub
end if
....
....
next I
end sub


and then in the button click code, re-enter that routine, or whatever else
you need.

With a userform, as soon as the current macro finishes, as long as the form
is still visible, the app is effectively in pause.
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

In other words:
If I add "Exit Sub" inside a "for-next". and then call the routine
"CheckCells()" again from the button "continue" (in a Form), it will continue
with the next "I" values that was paused from the previous routine or it will
start from I=1 ???

Thanks

Coco

 

[Bob Phillips]

No it will start from 1 again, if you want it to restart, you need to
program that. An example

Private StartLoop As Long

sub checkCells()dim errorFound as Boolean

If StartLoop = 0 Then StartLoop = 1

for I = StartLoop to 500
....
....
errorFound = FindErrors()
if errorFound = True then
frmCheckCells.lblErrorMessage.Caption = "Error found for i = "& i
StartLoop = i
Exit Sub
end if
....
....
next I
end sub

Here StartLoop will be decalred as a module level variable, that is before
any code in the module. Anywhere you want to restart the loop, just set the
variable to 0 or 1, otherwise just let it take care of itself.

--

HTH

RP
(remove nothere from the email address if mailing direct)