Showing a UserForm

Add to Favorites
Author:

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 AltF11 to go to the VBA window.
    c20937e1f48a6e641b006693ffbc900f.png
  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 CtrlR or go to View > Project Explorer.)
    a7c7d9fa2eb925740c385e38b3062b97.png
  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.
    7f2b924e901330deb02678c50f56b9a9.jpg

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.

818785ac2878af87ce4fced718ffd3d9.png

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:

619cd7cce0b2b578493f98b2c58a2246.png

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.

e018d8f3500be98d7818d4919e1d8987.png

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.

3e8cb94e5be70c9572bbba6a096f7d9a.png

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
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...
Modeless UserForm
Tutorial: Modeless UserForms allows you to interact with Excel while the form is open and visible. T...
Reset All Values in a UserForm
Tutorial: Clear or reset all values in a UserForm so that they contain only the default original val...
What is a UserForm in Excel?
Tutorial: A UserForm is basically a pop-up window that you can use to create a custom interface for ...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
Select Ranges in the Worksheet from a UserForm
Tutorial: Select a range in Excel from a UserForm and have that range input into the form so that yo...
Tutorial Details
Downloadable Files: Excel File
Similar Content
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...
Modeless UserForm
Tutorial: Modeless UserForms allows you to interact with Excel while the form is open and visible. T...
Reset All Values in a UserForm
Tutorial: Clear or reset all values in a UserForm so that they contain only the default original val...
Excel Forum