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

6
votes

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!!

2
excelformsvbabutton
Show the form without making it modal UserForm1.Show(False) – Ralph
Thanks for the suggestion, but when I do that the form appears blank and unresponsive. Is there something else I have to do? – Steven
try putting a DoEvents after calling the userform. – Scott Craner
Damn @Scott is fast. :) – vacip
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

2 Answers

8
votes

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.

1
votes

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