Modeless UserForm

Add to Favorites
Author: | Edits: don

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.

Sections:

How to Make a Modless Form By Hand

How to Make a Modless Form with Code

Meaning of Modeless UserForms

Notes

How to Make a Modless Form By Hand

VBA Window (Alt+F11) > Select the UserForm > Look to the Properties window (F4) > Change the ShowModal property to False.

ba9dbd1cb2fd8716226fddeaf578afc9.png

How to Make a Modless Form with Code

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.

Meaning of Modeless UserForms

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.

Default Modal Form with MsgBox

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.

Modeless Form with MsgBox

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.

6c85857f9ce37e374090c2f9f3d82de1.jpg

Notes

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
UserForm Properties List and Explanation
Tutorial: This is a listing of all properties for the UserForm in Excel. Each property includes an e...
Make a UserForm in Excel
Tutorial: Let's create a working UserForm in Excel. This is a step-by-step tutorial that shows you e...
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
UserForm Controls
Tutorial: This tutorial gives you an overview of what a UserForm Control is and how to add them to y...
Use Macros with UserForms
Tutorial: This tutorial explains how macros interact with UserForms. This includes an explanation of...
Showing a UserForm
Tutorial: How to display a UserForm in Excel and some common things you might want to do when you di...