Showing a UserForm

How to display a UserForm in Excel and some common things you might want to do when you display it.

Sections:

Manually Display the UserForm

Use a Button to Show the UserForm

Display the UserForm when Excel Starts

Notes

Manually Display the UserForm

Display the UserFrom by hand, without using any code.

1. Hit Alt + F11 to go to the VBA window.
   
2. If you don't immediately see the UserForm, as in our example in Step 1, then look to the Project window and double-click the UserForm from there. (If you don't see the Project window, hit Ctrl + R or go to View > Project Explorer.)
   
3. Once you see the UserForm, simply click anywhere on the form and hit F5 or click the play button at the top of the screen.
   

This is not the preferred method for opening a UserForm, but it is useful to know.

(If you don't have the Close button working yet in your form, you can hit the red X in the upper-right to close it. This is covered in more depth in the tutorial on closing UserForms.)

Use a Button to Show the UserForm

This is the most-often used method for showing a UserForm and it doesn't require much work.

Create a macro to display the UserForm:

Sub ShowUserForm()

UserForm1.Show

End Sub

The code above is simply the name of the userform (UserForm1) followed by .Show

To find the name of the form, go to the VBA window and look at the (Name) property from the Properties window.

Make sure you click on the form itself and not one of the Controls on the form, otherwise, you will see the properties for that Control instead of the form itself.

If you don't see the Properties window, hit F4.

Once you have the macro made, we can go ahead and attach it to a button in the worksheet.

Create a button in Excel:

Right-click the button > click Assign Macro > choose the macro we just made from the list and hit OK.

When you click the button, the form will now appear. (Note, if macros are turned off the form will not appear.)

If you would like more information on attaching macros to buttons, you can read our tutorial on running a macro when you click a button in Excel.

Display the UserForm when Excel Starts

When need a small macro to do this:

Private Sub Workbook_Open()

UserForm1.Show

End Sub

This code must go into ThisWorkbook and not in a regular module. In the VBA window, double-click where it says ThisWorkbook and a code window will open.

In the above code UserForm1 is the name of the UserForm that you want to open. To find the name of the form, go to the VBA window, click on the desired UserForm, and look at the (Name) property from the Properties window.

Once you put the code into ThisWorkbook, you must save the file as a macro-enabled workbook and then close and re-open it. When you open the workbook again, the form will only apear once you have enabled macros to run.

Notes

As you can see, there are a number of ways to have a UserForm open in Excel, and which method you use really depends on what you are doing. I open UserForms manually when I am building them, with a button once everything is finished, and on startup only if that's required by the project.

Make sure to download the sample file for this tutorial to see these examples in action.