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.
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
Video: UserForm with Combo Boxes – Demo
This video shows how to use my sample Excel UserForm with dependent combo boxes. You’ll get a peek behind the scenes too, on how the UserForm works.
Video Timeline
- 0:00 UserForm Demo
- 0:53 Lookup Lists Sheet
- 1:25 See How Macro Works
- 1:48 Named Range for Parts List
- 2:02 UserForm Macro Code
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
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
- 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.
frmParts.Show
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.
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.
_____________________
Show Excel UserForm Automatically When Workbook Opens
_____________________
Thanks it worked for me.
I really value your teaching and Its my first time using macros and you made it so easy. I recently used the option to make a form generate each day, using your instructions for automatically create a monthly forms, instead i did it for daily forms. So i created a set type of form and every day the form opens it gives a new day count. So the sheet is renamed with the day number. So if its August 11, the form would say 11. This is exactly what I want, but I need the new form to be progressively added. That is I need to see each day sheets. So I want to see the sheet for each day. So I want to see sheet 1, 2, 3, 4, 5, ……. until the end of the month progressively being generated. Can you assist me with this coding in Visual Basics. Thanks
Detailed, simple and useful explanation. Worked well. Thanks for it.
I am somewhat new in VBA. Trying to design automation exactly in the sense of the topic I managed myself to come to this simple solution with Workbook_Open() and UserForm.Show. However, later I had encountered some problem of another kind (probably with the scope of visibility for arrays) and come across a message from Microsoft named “VBA code “behind” a worksheet or a workbook may not work in Excel”. It suggested to cut the code from “behind” a workbook as in your case and paste it to a newly created module. I did so and everything stopped working. After a day (!) of torture I decided that Workbook_Open event is not functional in the newly created module. I dropped this advice, returned the code to the “behind” workbook place and life returned to my being.
Just for curiosity – why a trusted source offers such strange recommendations? Probably I do not catch something…