Pages

Show Userform Automatically When Opening Excel File

In Excel VBA applications, we often need to automatically show userforms when opening the Excel files. Suppose we have a simple VBA userform in our Excel file like this.

VBA Userform

This form is used to enter the Order information. Users can fill all the fields and click the “Enter” button. Then all the information will be written automatically to the next empty row of the data sheet. Also if needed, we can hide the data sheet from the users to protect its data. Then the question is how do we show this form to the users? There are few ways to show the userform to the users. We can create a simple macro to show the form. Then users can run that macro from the developer tab to see the form. But this method would not be user friendly. Because the “Developer” tab is not even visible by default in Excel. Our next option is to create a simple button in a worksheet and assign that macro to the button. Then the user can click the button to open the form. But my preferred method is showing the form automatically when opening the file. Then no extra action is needed from the user. This is how to configure it.

First, Open the VBA editor. You can use shortcut keys Alt + F11 to open the VBA Editor.

Open VBA editor

VBA Project explorer window will show you all the available modules. Double click on the ThisWorkbook module.

Double click on ThisWorkbook

There are two dropdowns above the code window. Select “Workbook” from the first dropdown.

Select Workbook

When you select the “Workbook” second dropdown will automatically change to “Open”. Also Workbook_Open procedure will be automatically added to the code window.

Workbook_Open procedure is added

Now we can write the code to show the form inside this procedure. We can simply do that using the below line.

UserForm1.Show

Final Workbook_Open procedure

Here UserForm1 is the name of the form. You should replace it with the name of your form. Now form will show up whenever the user opens the file.