Modeless UserForms allows you to interact with Excel while the form is open and visible. This means that you can select cells, enter data, move to other worksheets, run new macros, and do everything you would normally do in Excel, all while the form is open and visible.
By default, UserForms are Modal, which means that when they are open you cannot do anything else in Excel except to interact with that form; this also means that no macros will run unless initiated by the form that is visible. This is the normal operation for forms.
Here, you will learn how to make Modeless UserForms, which will include how to do it with and without VBA.
How to Make a Modless Form By Hand
How to Make a Modless Form with Code
VBA Window (Alt+F11) > Select the UserForm > Look to the Properties window (F4) > Change the ShowModal property to False.
UserForm1.Show vbModeless
vbModeless is what tells Excel to make the form Modeless.
You put this after the line that you use to display the form.
In this example, this code is contained within a module and looks like this:
Sub ShowUserForm()
UserForm1.Show vbModeless
End Sub
You do not have to put any additional code within the code section for the UserForm itself; merely add vbModeless after .Show in the macro that is used to show the form.
In the sample file for this tutorial, this particular code is attached to a button that runs the form.
When you have a Modal form, the default kind of form, where you cannot do anything in Excel while the form is visible, no code in Excel will be run unless initiated by the UserForm.
This means that if you have any code after the line that displays the form, none of that code will run until after the form is closed.
However, if you use a modeless form, code within Excel will continue to run after the form is made visible.
We can test this by putting code for a small popup window after the code that runs the form.
Sub ShowUserForm()
UserForm1.Show
'Try to display a message box after the form is initialized.
MsgBox "Hi!"
End Sub
Run this code and the message box will not appear until after you close the form.
Sub ShowUserForm()
UserForm1.Show vbModeless
'Try to display a message box after the form is initialized.
MsgBox "Hi!"
End Sub
Run this code and you will see the msgbox appear over the form because code execution was not paused while the form was displayed.
If you are not familiar with how to run or startup a UserForm, please read our tutorial on showing a UserForm.
The concept of Modal and Modeless forms might be a little confusing at first, so I highly recommend that you download the sample file and play around with these examples. The code to launch the form is placed within a regular macro in a module and attached to a button within the worksheet.