To help users enter data in a workbook, you can create an Excel UserForm, with text boxes and combo boxes. With a quick bit of code, you can show UserForm when Excel file opens.
In the original UserForm sample file, there was a button on the worksheet, and users could click it to open the UserForm.
In some workbooks, you might want people to get right to work, and see the UserForm immediately. So, instead of putting a button on the worksheet, you can show the UserForm automatically, when the file opens.
Open the Form Automatically
To make the UserForm open automatically, you’ll add a bit of code to the workbook’s code module, in the Workbook_Open procedure.
To add the code:
- In the UserForm workbook, press Alt + F11, to open the Visual Basic Editor.
- At the left, in the Project Explorer, find the UserForm workbook, and double-click on its ThisWorkbook module.
- At the top left of the main code window, from the Object drop down, select Workbook
- At the top right, from the Procedure drop down, select Open
- The Workbook_Open procedure will be automatically created.
- Where the cursor is flashing, type a line that opens the UserForm. In this example, the UserForm is named frmParts:
Test the Workbook_Open Code
To test the Workbook_Open code:
- Save and close the UserForm workbook.
- Open the workbook, and enable macros, if prompted.
- The UserForm will open automatically.