How to insert a "wait for" window in Excel without stopping code 1

[polocar]

Hi,
I'm developing some macro in an Excel file.
As one of these macro (when the user clicks on a button) requires 20 - 30 seconds of execution, I would like to create a "wait for" window for the user... The window is loaded and shown at the beginning and is unloaded at the end of the process.
In the Visual Basic Editor I right clicked in the VBA Project window, and I selected Insert -> UserForm (a UserForm1 has been created).
In this form I have put a simple label with caption "Wait for..." (nothing else).
At the beginning of Button1_Click, I inserted the statement:

Load UserForm1
UserForm1.Show

and, at the end:

UserForm1.Hide
Unload UserForm1

But the execution stops at UserForm1.Show statement (while I would like it executed the following code of the Button_Click procedure...)
I have read that there is a DoEvents statement that allows to bypass this problem: I have tried to insert it after UserForm1.Show statement, but no results, and then in the UserForm_Activate procedure, but also in this case no results...

Can you suggest me how to do it?
Thank you very much

 

[combo]

You can try to mix modeless userform and Application.Interactive=False:

Dim aForm As UserForm1
Set aForm = New UserForm1
aForm.Show
Application.Interactive = False
For i = 1 To 10000
    aForm.Caption = i
    aForm.Repaint
Next i
Application.Interactive = True
Unload aForm
Set aForm = Nothing

combo

 

[polocar]

Ok combo, thank you for your suggestion.
This weekend a friend explained me also another way, that is: open the window in a modeless way (the default way is modal), and than repaint the form, so I can write

Public Sub OpenWaitForForm()

Load WaitForForm
WaitForForm.Show vbModeless
WaitForForm.Repaint

End Sub

Public Sub CloseWaitForForm()

Unload WaitForForm

End Sub

Thank you very much

 

[combo]

That's what I wrote, you can set form modeless in design time, in its properties window (ShowModal set to False).

combo

 

[polocar]

You are right, I focused on your code, and didn't know that I can specify the modeless setting at design time...
Sorry