How to keep a code running in VBA while a form window is open?

Question

In Excel VBA, I want to push a button, run some code, and while the code is running have a form window appear saying something like "Code is running, please wait until finished. Click stop button to terminate code."

But every time I .Show a form the code stops, waiting for the form window to be closed before continuing. Is there a way to force the code to continue to run while the form window is open? Thanks!

Answered: Add (False) at the end of .Show or change the form's ShowModal property to False. Then add Application.Wait (Now + TimeValue("0:00:01")) just after fmRunning.Show(False). Or insert "DoEvents" instead of the Application.Wait Thanks all!!

Show the form without making it modal UserForm1.Show(False) — Ralph
– Ralph, Commented Feb 18, 2016 at 14:48
Thanks for the suggestion, but when I do that the form appears blank and unresponsive. Is there something else I have to do? — Steven
– Steven, Commented Feb 18, 2016 at 15:05
I added DoEvents, I get the same problem except now the window doesn't close at the end of the code like it used to, but at least after the code is done the form populates with text (#trolledByCode). Here is how I have it: Sub codename() DoEvents fmRunning.Show(False) 'I have also tried it without the false and 'instead with vbModeless and another time setting it's properties to 0. ... ... fmRunning.Close End Sub — Steven
– Steven, Commented Feb 18, 2016 at 15:18

vacip · Accepted Answer · 2016-02-18 15:12:31Z

9

You need to set the userform to be modeless. This can be done two ways:

either by selecting the userform and changing the ShowModal property to False
or by opening the userform with setting the modal property to 0 (vbModeless)

Userform.Show vbModeless

See MSDN for more info.

edited Feb 18, 2016 at 15:12

answered Feb 18, 2016 at 14:56

vacip

5,4563 gold badges29 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steven Over a year ago

Thanks for the suggestions, but when I do either of those the form appears blank and unresponsive. Is there something else I have to do?

elano7 · Accepted Answer · 2020-06-03 13:32:20Z

1

Using only Userform.Show vbModeless can cause blank form. You should also use the command Userform.Repaint. This will refresh the info on the form. Example:

Userform.Show vbModeless
Userform.Caption = "Some text"   'change the Caption text
Userform.Repaint   'refresh changes

This way you should see the changes

answered Jun 3, 2020 at 13:32

elano7

2,3221 gold badge22 silver badges23 bronze badges

Comments

Rajitha Kithuldeniya · Accepted Answer · 2021-11-25 02:43:49Z

0

Select the form in the Project panel.
Change the ShowModal to false

this will keep your code running even the form is opned

answered Nov 25, 2021 at 2:43

Rajitha Kithuldeniya

7318 silver badges8 bronze badges

Comments

Suraj Shourie · Accepted Answer · 2023-09-01 02:37:15Z

0

If all you are doing is advising the user to wait then you don't need an extra form. Simply add a label to the form that say "Please wait...". Set the label as hidden until the button is pressed then make it visible at the start of the code and hide it again when the code finishes. You might need to insert DoEvents straight after the visible = True instruction as it might be overtaken by the main section of code.

me.label1.visible = true/false

edited Sep 1, 2023 at 2:37

Suraj Shourie

2,60511 silver badges19 bronze badges

answered Aug 22, 2023 at 6:36

Keith Miller

11 bronze badge

Collectives™ on Stack Overflow

How to keep a code running in VBA while a form window is open?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related