Home > Macros > UserForms > Open

Open an Excel UserForm

To make it easier for users to enter data in a workbook, you can create an Excel UserForm, using Excel VBA code. This page shows 4 different ways to open a UserForm, for testing, or to enter data.

Tip: There are written steps and videos, for starting a UserForm from scratch, on the Excel UserForm - Basic page

Run Excel UserForm in Visual Basic Editor

How to Open Excel UserForm - 4 Ways

This page shows 4 different ways to open an Excel UserForm, after you've built one in an Excel workbook:

  1. VBE: Open the UserForm in the Visual Basic Editor (VBE) window, while you build the UserForm, or make changes to it
  2. Button: Make a worksheet button that people can click, to quickly open the UserForm
  3. Shortcut: Set up a keyboard shortcut that opens a UserForm. It lets you keep your hands on the keyboard!
  4. Automatic: Add a simple line of Excel VBA code, to automatically open the UserForm when its workbook opens. This makes it easy for people to get right to work, if they're entering data!

UserForm with TextBoxes and worksheet button

Video: Basic UserForm - Demo

In the sample file that you can download below, there is a Data Entry UserForm with Text Boxes. In the short video below, I'll show you how that UserForm works, before you explore the 3 different ways to open the UserForm.

Note: For written steps for building the UserForm, and step-by-step videos, go to the Excel UserForm - Basic page. .

1) Open Excel UserForm in VBE

This video shows how to open the UserForm in the VBE. There are written steps below the video.

Open Excel UserForm in VBE

To open an Excel UserForm while you're building it in the Visual Basic Editor (VBE), follow the steps below:

  • Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.
  • On the Menu bar, choose Run | Run Sub/UserForm.
    • OR, use the keyboard shortcut F5, top open the UserForm

Run Excel UserForm in Visual Basic Editor

Test and Close UserForm

The UserForm opens, and you can test the text boxes, combo boxes and buttons.

After you finish testing the UserForm, click the 'Close form' button, at the bottom right of the form, to return to the VBE.

Click the Close Form button on the Excel UserForm

2) Open with Worksheet Button

This video shows how to add a button to the worksheet, to open the UserForm, and hide the database sheet. There are written steps below the video.

Worksheet Button to Open UserForm

To make it easy for users to open the UserForm, you can add a button to an Excel worksheet.

First, follow these steps to get started with the worksheet button:

  • In the Excel workbook, go to the worksheet where you want to add the button
    • Tip: Add a new worksheet, if you prefer.
    • In the sample file, the sheet name is Parts Data Entry
  • On the Excel Ribbon, go to the Insert tab
  • Click the drop-down arrow for Illustrations
  • Click the drop-down arrow for Shapes
  • Click on the shape you'd like to use for the button
    • I usually use a rectangle or rectangle with rounded corners

Select shape for worksheet button

Add Button to Worksheet

  • On the worksheet, click where you'd like the top left corner of the button to be located
  • A shape with the workbook's default colour settings is added to the worksheet.
  • On the Excel Ribbon, go to the Shape Format tab, which appears when the shape is selected.
  • Choose a Shape Style from the pallette, or select Shape Fill and Shape Outline colours.

Add Button Text

Next, follow these steps to add text to the button

  • On the worksheet, click on the button, to select it
  • Next, right-click the shape, and click on Edit Text
  • Type the text label you want for the button.
    • In the sample file, I used this text: Click here to add Part Information
  • Next, click the border of the button, to exit the Text area
  • On the Excel Ribbon, go to the Home tab
  • Choose the Font and Alignment settings that you want for the button text
    • For the button shown below, I used Bold, Calibri, 14 pt, centred vertically and horizontally

worksheet button with formatted text

Add Button Macro

To make the button open the form, follow these steps, to create a macro:

  • Right-click anywhere on the button, and click the Assign Macro command
    • The Assign Macro dialog box opens
    • In the Macro name box, the shape name is combined with "Click", as the default macro name.
  • In the Macro name box, delete the default name, and type a short meaningful name for the macro.
    • In the sample file, I used the name OpenPartUF
  • Click the New button, to open the Visual Basic Editor (VBE)

Find UserForm in VBE

When the VBE opens, at the left, you should see the Project Explorer, which lists all the workbooks you have open – even hidden workbooks, such as add-ins.

  • Tip: If you don't see the Project Explorer, click the View menu, then click Project Explorer, to open it.
  • In the Project Explorer, find your active workbook
  • To the left of the workbook name, click the plus sign, if there is one, to see all the objects and subfolders
  • To the left of the Forms folder, click the plus sign, to see the UserForm objects, and their names.
  • Make note of the UserForm name -- frmParts in the sample file

macro code to open Excel UserForm

Add Code to Macro in VBE

Next, you'll add a line of code in your macro, referring to your UserForm's name.

  • At the right, in the code window, you should see a line with "Sub" and the name that you gave your macro
  • Click in the blank line below that, to put the cursor in that line
  • Next, type the name of your UserForm, e.g. frmParts
    • Tip: Spell the name correctly, but upper and lower case don't matter
    • I like to use lower case. Then, when I click in another line of code, the name should change to the correct case, if it was spelled correctly!
  • After the name, type a period (do not leave a space before the period)
  • A drop-down list of options should appear. These are the methods and properties you can use for programming with an Excel UserForm
  • With the drop down list open, type Show -- the list will find the matching commands while you type
    • Tip: When Show is highlighted in the list, press the Tab key to add the highlighted word to the macro's code

macro code to open Excel UserForm

Finish the VBA Code Change

To complete the macro change, follow these steps:

  • In the macro code, click in the End Sub line, to move the cursor there
  • In the line of code that you added, the form name should change to the correct upper and lower case automatically. That's a good way to check your spelling!
  • At the top left of the VBE window, click the Save button
    • or use the keyboard shortcut, Ctrl + S
  • Finally, click the File menu, and click the Close and REturn to Microsoft Excel command
    • or use the keyboard shortcut, Alt + Q

Test the Worksheet Button

After you set up a worksheet button that runs a macro, it's good practice to test the button.

  1. Go back to the Excel worksheet where you added the button
  2. On the worksheet, click the button, to make sure that the macro runs correctly, and it opens the UserForm.

Run Excel UserForm in Visual Basic Editor

Change UserForm Setting (Optional)

While the Excel UserForm is open in your workbook:

  • You won't be able to perform any other actions in Excel, such as entering data on the worksheet.
  • You'll have to close the form first.

By default, the UserForm opens as "Modal", which keeps the focus on the UserForm.

Allow Worksheet Use

If you want users to be able to perform other actions in Excel while the form is open, you can make a small change the macro code.

To edit the macro code, follow these steps:

  • Right-click the worksheet button, and click Assign Macro
  • In the list of macros, click on your macro name, then click the Edit command
  • In the macro code, click at the end of the line that you typed:
  • frmParts.Show
  • Type a space character, and a yellow pop-up tooltip should appear. It shows that you can use the Modal argument with the Show method.
  • Type false for that argument, then click in line below.
    • Run Excel UserForm in Visual Basic Editor
  • The first letter should automatically change to upper case, if you typed the word correctly.
  • Use the Ctrl + S shortcut to save the change, then return to Excel.

Test the Macro Change

Next, test the worksheet button again, to open the form.

Because the Modal setting was turned off (False), you should be able to make changes on the worksheet, while the UserForm is open.

3) Open with Keyboard Shortcut

If you like to use keyboard shortcuts in Excel, you can set a shortcut for the macro that you created, for the worksheet button.

To add the shortcut, follow these steps:

  • On the Excel Ribbon, go to the View tab
  • At the far right, click the Macros command
  • In the list of macros, click on your macro name, then click the Options button
  • In the Macro Options dialog box, click in the small white box, for the Shortcut Key.
  • To enter a shortcut, you'll press:
    • the Shift key (optional)
    • and any letter of the alphabet (required)
  • I used the shortcut Ctrl+Shift+U
    • WARNING: Don't choose a shortcut that will override a built-in Excel keyboard shortcuts, like Ctrl+S (save) or Ctrl+P (print)
  • Click OK to create the shortcut
  • Back in the Macro list, click the Cancel button, to exit (this will NOT cancel the shortcut that you just created.)

Run Excel UserForm in Visual Basic Editor

Test Keyboard Shortcut

Back in Excel, to use your new shortcut, you'll press:

  • the Ctrl key (required)
  • and the Shift key (if used)
  • and the letter of the alphabet you chose (required)

4) Open UserForm Automatically

Instead of using a worksheet button to open the UserForm, you can use an "event" macro, to show the UserForm automatically, when the workbook opens.

For this example, the code will run your Worksheet button macro, after the “event” of opening a workbook.

Start the Event Code

Here’s how to add the code to open the UserForm automatically. 

First, follow these steps to open the workbook's code module:

  • In the UserForm workbook, press Alt + F11, to open the Visual Basic Editor (VBE)
  • At the left, in the Project Explorer, find the UserForm workbook
  • At the left of the workbook name, click the plus sign, to see the workbook contents.
  • Next, right-click on the ThisWorkbook module, and click View Code

View code for ThisWorkbook object

Create the Workbook Open Code

Next, follow these steps to add code to the workbook module:

  • The code module for ThisWorkbook opens at the right side of the VBE
  • At the top left of the code module window, click the drop down arrow, and click on Workbook

View code for ThisWorkbook object

  • In the code module, an Workbook_Open procedure is automatically added, with the cursor positioned in the blank line.

Workbook)Open procedure started

  • Where the cursor is flashing, type the name of your Worksheet Button macro, e.g. OpenPartUF.

Workbook)Open procedure with macro name

Test the Macro

To test the Workbook_Open code, follow these steps:

  • Save and close the UserForm workbook.
  • Open the Excel workbook again, and enable macros, if prompted.
  • The UserForm should open automatically.

Open Visual Basic Editor

After you build an Excel UserForm, you can make changes to it in the Visual Basic Editor (VBE) window

To open the Visual Basic Editor, follow these steps:

  • Use the keyboard shortcut - Alt+F11
  • OR, on the Excel Ribbon, click the Developer tab (if it's not visible, see steps in the section below)
  • Then, in the Code group, click Visual Basic.

The VBE opens, and at the left, you should see the Project Explorer, which lists all the workbooks you have open – even the hidden workbooks, such as add-ins.

  • Tip: If you do not see the Project Explorer, click the View menu, then click Project Explorer, to open it.

Find your UserForm, and double-click on it, to see the form in Design view.

Video: Show Developer Tab

UserForms are created in the Visual Basic Editor (VBE), where you can see the Visual Basic for Applications (VBA) code.

  • Before you start working in the VBE, check the Excel Ribbon, to see if it shows the Developer tab.
  • If you do NOT see a Developer tab on the Excel Ribbon, follow the steps in this video, to show the Developer tab in Excel.

Get the Sample File

Download the Open Excel UserForms sample workbook, to test the different methods of opening an Excel UserForm. The zipped file is in xlsm format, and contains macros. Enable macros, when opening the file, if you want to test the code.

More UserForm Tutorials

Excel UserForm - Basic

UserForm with ComboBoxes

UserForm Dependent ComboBoxes

UserForm ComboBox VBA

UserForm TextBox Validation Code

UserForm with Help Pages

 

Last updated: November 22, 2023 9:29 PM