How to Exit a "Do While" Loop

Posted by Dexter on August 30, 2001 11:56 AM

I have a simple application that permits an operator to select from a list of choices in a dialog box; the choice copies data into the active cell. The active cell subsequently moves down one row, and another selection and copy can be made.

I would like to change this loop from a "For-Next" to a "Do While" or a "Do Until" with an option button in the dialog box that permits the operator to quit the routine at any time. Here is the existing code:

Range("E10").Select
For DayToSched = 1 to 31
Application.DialogSheets("Dialog1").Show
Range("B1").Copy
ActiveCell.PasteSpecial Paste:=xlValues
ActiveCell.Offset(1,0).Select
Next DayToSched
End Sub

Any help on this would be greatly appreciated

Posted by Damon Ostrander on August 30, 2001 3:03 PM

Hi Dexter,

Just create a global variable at the top of your module, and in your dialog's code just before unloading the dialog set the value of the global variable to the option button value (Value property). This saves the option button value so that you know what it was after unloading the dialog. Then you can put in your loop a test that exits the loop if the option button value was True.

Example:

Public AllDone As Boolean

... macros ...

Range("E10").Select
For DayToSched = 1 to 31
Application.DialogSheets("Dialog1").Show
If AllDone Then Exit For
Range("B1").Copy
ActiveCell.PasteSpecial Paste:=xlValues
ActiveCell.Offset(1,0).Select
Next DayToSched
End Sub

Incidentally, this example is for a For loop, not a Do While as in your subject line. If you want to exit from a Do While replace the Exit For with Exit Do.

I hope this helps.

Damon

Posted by Damon on August 31, 2001 3:44 AM

Many thanks for your help. I'll give it a try at once.

Thanks
Dexter

Posted by Dexter on August 31, 2001 5:08 AM

Posted by Dexter on August 31, 2001 5:09 AM

Damon,

I guess I did not have full understanding afterall. I don't understand the Option Button Variable that you described. Could you clarify?
Thanks
Dexter

Posted by Damon Ostrander on August 31, 2001 8:27 AM

Hi again Dexter,

You had mentioned that you had an "option button" in the dialog box, so I assumed you meant an OptionButton object (sometimes called a "radio button"--sort of like a checkbox, but round and permits selection of just one of a set of options). Perhaps you just meant a Button object that you use to select options. If this is the case, then set AllDone to True in this button's Click event macro.

I am assuming that you are using Excel 5, because new versions of Excel no longer support creation of Dialog sheets.

Damon