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.
This screen shot shows a UserForm with combo boxes, where you can select from a list of items.
When you click the “Add this part” button, the data is copied to the first blank row on a hidden worksheet – PartsData.
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.
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
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
- At the top left of the code module window, click the drop down arrow, and click on Workbook
A Workbook_Open procedure is automatically added to the code module, with the cursor positioned in the blank line.
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.
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
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.
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.
Show Excel UserForm Automatically When Workbook Opens