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
This page shows 4 different ways to open an Excel UserForm, after you've built one in an Excel workbook:
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:
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.
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.
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:
Add Button to Worksheet
Add Button Text
Next, follow these steps to add text to the button
Add Button Macro
To make the button open the form, follow these steps, to create a macro:
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.
Add Code to Macro in VBE
Next, you'll add a line of code in your macro, referring to your UserForm's name.
Finish the VBA Code Change
To complete the macro change, follow these steps:
Test the Worksheet Button
After you set up a worksheet button that runs a macro, it's good practice to test the button.
Change UserForm Setting (Optional)
While the Excel UserForm is open in your workbook:
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:
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:
Test Keyboard Shortcut
Back in Excel, to use your new shortcut, you'll press:
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:
Create the Workbook Open Code
Next, follow these steps to add code to the workbook module:
Test the Macro
To test the Workbook_Open code, follow these steps:
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:
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.
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.
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
Last updated: November 22, 2023 9:29 PM