Background
When creating tools in Excel, I often want to simplify user input and operations. Therefore, I use UserForms in my programs.
I wanted to be able to open a completed UserForm by clicking a button on the Excel sheet, so I learned how to do it.
Specifications
- Place a shape button on the Excel sheet.
- Clicking the button displays the created UserForm.
- Assumption: The UserForm has already been created in VBA.
Implementation Steps and Code
1. Create a Button (Shape)
First, place a shape on the Excel sheet.
- Excel Ribbon -> Insert -> Shapes
- Choose a shape like a “Rectangle” and place it on the sheet.
- Enter a clear label inside the shape, such as “Show Form”.
2. Write Code in a Module
Next, open the VBA editor and write the following macro in a Standard Module (e.g., Module1). In this example, I assume we are displaying “UserForm1”.
Sub ShowUserForm()
UserForm1.Show
End Sub
Note: Please change UserForm1 to match the name of your actual UserForm.
3. Assign the Macro to the Shape
- Right-click the shape.
- Select Assign Macro and choose the “ShowUserForm” macro created above.
- Click OK.
Now, clicking the shape will pop up the UserForm.
Note: If you haven’t created a UserForm yet
You can easily add a UserForm from the VBA editor by going to Insert -> UserForm. By placing text boxes and buttons and adding event processing, you can use it as an input screen or a management screen.
Summary
- To call a UserForm from an Excel sheet, simply write a macro in a standard module and assign it to a shape button.
- You can display the form using
UserForm1.Show. - You can create a form input system using VBA without building a complex UI.
This method is highly recommended for those who want to improve daily work efficiency or create easier-to-use input tools.
