Using Multiple Custom Forms or UserForms (Modal Window), Excel VBA

Modal Window

A modal window is a window that cannot be closed without interacting with it first. By default, a custom window in VBA is modal. You can define the window type (modal or modeless) using the optional style parameter of the Show method.

Show Style

The style parameter has two valid values:

• vbModal or 1 for a modal form,
• vbModeless or 0 for a modeless form.

For example, with the following statement, the form window is displayed on the worksheet in modeless mode, and therefore, when the window is open, the user has access to the worksheet cells:

UserForm1.Show vbModeless

When the window is launched with the following statement, it is in modal mode, and therefore the worksheet cells are inaccessible to the user until the window is closed:

UserForm1.Show vbModal

Using Multiple Custom Forms

There can be multiple custom forms in a project. When switching from one form to another, it’s important to consider the mode (modal or modeless) in which the form is opened.

For example, add two forms—UserForm1 and UserForm2—to your project. Create a button on the worksheet and set its Name property to cmdForm1. When you press this button, the first form window will appear on screen.

The two macros below show how the code to invoke the second form when the first is clicked must differ depending on whether the window type is modal or modeless.

In Modal Mode

Before displaying the second form, you must close the first one in the code. In this case, only one form is ever shown on screen—either the first or the second.

Worksheet Module Code:

Private Sub cmdForm1_Click()
    UserForm1.Show vbModal
End Sub

UserForm1 Module Code:

Private Sub UserForm_Click()
    Unload UserForm1
    UserForm2.Show
End Sub

In Modeless Mode

It is not necessary to close the first form, and after clicking on the first form, both forms will be displayed on screen. To ensure both forms are visible at the same time, the second form is positioned slightly offset from the first.

Worksheet Module Code:

Private Sub cmdForm1_Click()
    UserForm1.Show vbModeless
End Sub

UserForm1 Module Code:

Private Sub UserForm_Click()
    UserForm2.StartUpPosition = 0
    UserForm2.Top = UserForm1.Top + 20
    UserForm2.Left = UserForm1.Left + 20
    UserForm2.Show
End Sub