Home Stop a do loop with a command button
Reply: 2

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.373229 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO