ShowModal VBA vbModal and vbModeless

Introduction to VBA ShowModal
How to Use VBA ShowModal
vbModal UserForm Behavior
vbModeless UserForm Behavior

Introduction to VBA ShowModal

The ShowModal VBA property controls how a UserForm behaves when displayed. UserForms can either be shown as vbModal or vbModeless by setting the ShowModal property.

ShowModal Property

What is vbModal?

When the ShowModal property is set to True, which is equivalent to vbModal, the user must close or hide the UserForm before anything else in the application can occur. In other words, you can’t click cells in Excel and macros will not run until the user hides or closes the UserForm. The user is forced to do something with the open UserForm. When your UserForm behaves like this, it is said to be modal. This is the default mode.

What is vbModeless?

When the ShowModal property is set to False, which is equivalent to vbModeless, the user can still fully interact with the rest of the application. You can click cells in Excel. You can copy and paste ranges. You can do anything you normally could do in Excel. In addition to that, your macros will continue to run and you can even launch new macros. You’re not forced to close or hide your UserForm before running other macros. When your UserForm behaves like this, it is said to be modeless.

I use a modeless UserForm in my Mouse To Macro Excel Add-in to display the recording status and instructions for recording your mouse.

The rest of this tutorial teaches you how to use the ShowModal property and explains how the two modes behave differently.

How to Use VBA ShowModal

There are two primary ways to set the ShowModal property using Excel VBA. You can either do it at run-time using the VBA Show method, or you can manually set ShowModal to a value via the properties window of your UserForm. Let’s walk you through both methods.

VBA Show Method Example

Let’s say you have a UserForm named UserForm1. To call and display UserForm1 from a module, you would normally type UserForm1.Show. You may have done this a dozen times before, but guess what? The Show method allows for an optional argument! When you don’t enter the optional argument, your UserForm is shown modally.

Here’s how you can display your UserForm modelessly by providing an additional argument:

Sub ShowModalDemo()
UserForm1.Show vbModeless
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Remember earlier when I said you can display your UserForm with UserForm1.Show? This is the exact same as typing UserForm1.Show vbModal.

ShowModal Property Example

In this example, we’ll assume your UserForm is still named UserForm1. Instead of setting the mode of display using a macro, you can manually set the property using the UserForm Properties window.

Go ahead and make a UserForm and select it in your project explorer pane (Ctrl+R). If you don’t automatically see a Properties window, press the F4 key. Your screen may look something like this:

The Properties window is outlined in red. Once you see this window, scroll down until you see the ShowModal Property, again shown in red.

By default, the value is set to True (vbModal). By setting it to False, you are changing how the UserForm behaves. It will now be modeless (vbModeless).

Let’s take a closer look at the differences in Modal and Modeless UserForm behavior.

vbModal UserForm Behavior

To illustrate how modal UserForms act, I’m going to run the following macro:

Sub ShowModalDemo2()
UserForm1.Show vbModal
MsgBox "Another Window"
End Sub

Notice the keyword vbModal after the Show method. I want you to pay attention to what happens when I run this macro. Better yet, try to run the macro on your computer, too. You’ll see that the MsgBox will not appear until you close UserForm1. In other words, your macro completely stops until you do something with the open UserForm.

vbModeless UserForm Behavior

To show how modeless UserForms behave differently, I’m going to run the exact same macro, but with vbModeless instead of vbModal.

Sub ShowModalDemo3()
UserForm1.Show vbModeless
MsgBox "Another Window"
End Sub

Can you guess what will happen? The MsgBox and the UserForm should BOTH appear at the same time because modeless operation means the rest of your macro will continue to run while your UserForm is displayed.

This can be really useful if you have a progress bar or if you want to provide real-time status updates as your macro runs.

It’s worth noting that the MsgBox command is modal, so any code after you issue the MsgBox command will not execute until after you do something with your MsgBox window. I hope you don’t let this confuse you.

Alright, that’s all for today’s lesson on ShowModal. Hopefully, you feel comfortable using ShowModal and you understand the differences between vbModal and vbModeless.

Please, share this article on Twitter and Facebook. Sharing on social media is how I’m able to reach and teach more people about the awesome power of VBA.

I want to thank all my readers who have already subscribed to my free wellsrPRO VBA Training Program and I encourage you to go ahead and subscribe using the form below if you haven’t done so. You’ll love the great VBA content I send your way!