Home Stop a do loop with a command button

# Stop a do loop with a command button

Hunter B
1#
Hunter B Published in 2018-01-12 21:54:37Z
 I have a VBA excel userform that is misbehaving. Basically I want my code to pause until a command button on a userform is pressed. Any ideas? In a module: Public okayclicked as boolean Sub MyThing UserForm1.Show Do Until okayclicked DoEvents Loop UserForm1.Hide End Sub  In my userform: Sub CommandButton1_Click() okayclicked = True End Sub  Not sure what else to try to make this work. Any help is appreciated! Thanks!
AJD
2#
AJD Reply to 2018-01-13 04:19:50Z
 Either use a MsgBox to ask for the user input, or set your form to be Modal (MSDN link). A Modal form will allow you to continue to work in the form, and any code underlying the form will continue to run, but the main code that calls the form will be 'blocked'. Should the underlying Excel sheets be modified during the period of pause, you can add code when the button press is detected (i.e. when the Form is closed or then the MsgBox OK button is pressed) to update the relevant parts of the Form. But we have digressed from the original simple question and many other factors (such as how the underlying sheets will be modified when the form is open) come into play. Consider if a change in the underlying sheet has to change the form immediately or if it can occur when the user resumes the form/code. Consider if the functions you want can be done natively in the spreadsheet and minimise the use/functionality of the form. Consider other event driven options where the code is broken into natural sections, functions, subroutines or modules and each section only runs when triggered by the right event (sheet change, user input, subroutine finished, form exit etc.)
Tim Williams
3#
Tim Williams Reply to 2018-01-12 22:57:22Z
 'module Sub MyThing1 UserForm1.Show End Sub Public Sub MyThing2 '....rest of code End Sub 'userform Sub CommandButton1_Click() MyThing2 Me.Hide End Sub 
 You need to login account before you can post.
Processed in 0.373229 second(s) , Gzip On .