"Please wait - Processing" message

[stevetalaga]

I have a programme that takes some user input from a User Form and then updates the active document based on the details entered by the user. As the documents are quite large the updates take about 20 seconds to complete.

I would like to display a message to the user saying "Please wait while your updates are applied..." but when I use a MsgBox or a User Form to do this, the programme stops and waits for a response to the MsgBox or to the User Form.

Is there a way to display the MsgBox / User Form and have the programme continue executing behind the scenes and then remove the MsgBox / User Form after all the updates are done?

I have checked the Help file for options and looked for properties that may allow this but so far have drawn a blank.

One option I guess would be to use a User Form to display the "Please wait" message and have all of my main code inside that User Form so that it executes when the form opens but I'm sure there must be a tidier way of doing this.

[fumei]

One option I guess would be to use a User Form to display the "Please wait" message and have all of my main code inside that User Form so that it executes when the form opens but I'm sure there must be a tidier way of doing this. That is one way. You do not need to have the actual code in the userform module itself though. Just as long as it can be called FROM the userform. Since you are using a userform to get the input anyway, this is probably the easiest way.

Or you can put a message into the status bar.

Have you tried googling this, or even searching here. It is a common question.

[stevetalaga]

I did do a fair bit of Googling and searching the forum before posting the question but didn't think to search for "Status Bar". Found a solution idea here:
http://www.vbaexpress.com/forum/showthread.php?36466-Update-Status-Bar-or-Status-Bar-Alternative&highlight=status+bar

I have used a Modeless User Form to handle it and placed the message as the form caption since the form is greyed out and the controls in the form cant be seen clearly. It's not ideal but it does the job:
11466

[Paul_Hossler]

11474

If you don't mind a suggestion




Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub test()

Load ufWait

Call ufWait.Show(vbModeless)
DoEvents
Call Sleep(3000)
Call ufWait.hide

Unload ufWait
End Sub




Paul

[stevetalaga]

Hi Paul, always happy to learn from other suggestions. I tried your code and it works fine in your module but when I use it in my code it has the same problem where the controls on the "Please wait" user form are not displayed. This got me checking to see where the problem lies and I think it may be a memory problem of some sort.
11475

I used a simple MsgBox to pause processing at certain points in my code to home in on the problem and noticed that if I load the "Please wait" user form and immediately stop the processing with the MsgBox the controls in my "Please wait" form display correctly even after I close the MsgBox. If I let the code run without the MsgBox the controls in the "Please wait" form do not display.
<Trying to upload image of MsgBox and User Form but seems I'm limited to 1 image. Will post a second post with that image>

I kept moving the position of the MsgBox and the controls display properly when the MsgBox is positioned up to about line 1,000 of the code. After that the controls do not display. :banghead:

[stevetalaga]

All being well, here's the second image showing the MsgBox and the "Please wait" form with the form controls displayed correctly...
11476

[stevetalaga]

Yehey!! Fixed it.

Not sure why it is a problem in the first place but if I use .repaint after showing the form it displays correctly.

Still learning the basics and loving it!

[Paul_Hossler]

Glad you got it working (and that you like the learning part)

I added a DoEvents in my test case, but you might have some interaction with a .ScreenUpdating = False

Paul