Show Excel UserForm Automatically When Workbook Opens

You can add a UserForm to your Excel file, so it’s easy for people to enter data, without going to the table where the historical data is stored. Usually, I add a worksheet button that opens the form. But, with a simple macro, you can show Excel UserForm automatically, when workbook opens.

Excel UserForm

This screen shot shows a UserForm with combo boxes, where you can select from a list of items.

userformcombo01

When you click the “Add this part” button, the data is copied to the first blank row on a hidden worksheet – PartsData.

userformcombo02

Since the stored data is hidden away, it’s better protected from someone accidentally changing or deleting the records.

Set up Excel UserForm

If you’re not sure how to set up an Excel UserForm, there are step-by-step instructions on my Contextures site.

You can start with a simple form: Create a Basic UserForm
Or, make a form with drop down lists in combo boxes: UserForm with ComboBoxes
And for something even fancier, make dependent combo boxes – after you select from the first one, a list of related items appears in the second combo box: UserForm Dependent ComboBoxes

Open the UserForm

If you check out those UserForm instruction pages, I show how to add a worksheet button to open the UserForm.

Then, just click that button, and a simple macro runs, to open the UserForm.

userformcombo03

Show UserForm Automatically

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

In Excel programming, there are “events”, which are actions on a worksheet, or workbook, or other objects.

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

Start the Event Code

Here’s how to add the code to open the UserForm automatically. The first step is to find the name of the UserForm – we need to use that in the code.

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
To see the UserForm, click the plus sign at the left of the Forms folder, to open the folder
In this example, the UserForm is named frmParts

userformcombo04

Workbook_Open Event

Next, follow these steps to create the code that runs when the workbook opens.

In the Project Explorer, click the plus sign at the left of the Microsoft Excel Objects folder, to see the contents.
Right-click on the ThisWorkbook module, and click View Code

userformcombo05

At the top left of the code module window, click the drop down arrow, and click on Workbook

userformcombo06

A Workbook_Open procedure is automatically added to the code module, with the cursor positioned in the blank line.

userformcombo07

NOTE: If the procedure was not added, click the drop down arrow at the top right, and click on Open

Where the cursor is flashing, type the following line of code – use the name of your form, instead of frmParts.

frmParts.Show

userformcombo08

Test the Macro

To test the Workbook_Open code, follow these steps:

Save and close the UserForm workbook.
Open the workbook, and enable macros, if prompted.
The UserForm will open automatically.

Adjust the Macro

If you want to make changes on the worksheet, while the UserForm is open, you can make a small change to the Workbook_Open code.

Press Alt+F11, to open the VBE
Go to the ThisWorkbook code module
At the end of the line of code, type a space character. Intellisense will show a popup with the Modal property

userformcombo09

By default, that property is True, which means that you can’t do anything on the worksheet, until you close the UserForm

Type False, to change the form to modeless, so you can do other things, while the form is open.

frmParts.Show False

Then, save the workbook, to save the change to the code.

Download the Sample File

To get the UserForm workbook that was used in this example, go to the UserForm with ComboBoxes page on my Contextures site.

The zipped file is in xlsm format, and contains the UserForm and macros.

NOTE: The file does not have the Workbook_Open code yet, so you can follow the instructions above, to add it.

_____________________