VBA looping

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mdelta - 27 Oct 2006 08:37 GMT

I am working on a VBA script which I want to loop until it fills all
rows in excel, or the user stops it by clicking a button.

By hitting ESC it will take out all delays that I have in the script
and the script will finish off the number of "loops"--I would like to
have a "STOP" command button where the script stops then resets.

Any information that you can provide will be appreciated.

Thanks in advance
Mark

Reply to this Message

Ken Johnson - 27 Oct 2006 12:29 GMT

> I am working on a VBA script which I want to loop until it fills all
> rows in excel, or the user stops it by clicking a button.
[quoted text clipped - 7 lines]
> Thanks in advance
> Mark

Hi Mark,

This seems to work...

Public blnStop

Public Sub loopy()
blnStop = False
Dim I As Long
Do
I = I + 1
DoEvents
Cells(1, 1).Value = I
Loop While Not blnStop
End Sub

Public Sub StopLoop()
blnStop = True
End Sub

Assign the StopLoop Sub to a Forms button.
The DoEvents statement enables the button to be operable while the
looping code is running.

Ken Johnson

Reply to this Message

Mdelta - 27 Oct 2006 12:55 GMT

> > I am working on a VBA script which I want to loop until it fills all
> > rows in excel, or the user stops it by clicking a button.
[quoted text clipped - 31 lines]
>
> Ken Johnson

Thanks Ken

Reply to this Message

Ken Johnson - 27 Oct 2006 12:57 GMT

You're welcome Mark.

Thanks for the feedback

Ken Johnson

Reply to this Message

Ken Johnson - 27 Oct 2006 13:57 GMT

Hi Mark,
Just noticed I accidentally dimensioned blnStop as Variant.

It should be...

Public blnStop as Boolean

Trivial I know!

Ken Johnson

Reply to this Message