[Excel VBA] How to Display a UserForm by Clicking a Button

Background

When creating tools in Excel, you often want to simplify user input and operations. While UserForms are excellent for this, you need a way for the user to launch them easily from the worksheet.

This article explains how to link a VBA UserForm to a button (shape) placed on an Excel sheet.

Implementation Steps

1. Write the Macro Code

First, you need a simple macro in a Standard Module to trigger the form.

1. Open the VBA Editor (Alt + F11).
2. Insert a Module (Insert > Module).
3. Paste the following code:

Sub ShowUserForm()
    ' Replace "UserForm1" with the actual name of your form
    UserForm1.Show
End Sub

2. Create the Button (Shape)

Next, create the visual button on your Excel sheet.

1. Go to the Insert tab on the ribbon.
2. Select Shapes and choose a rectangle (or any shape you prefer).
3. Draw the shape on the sheet.
4. Type a label inside the shape, such as “Open Form”.

3. Assign the Macro to the Shape

Finally, link the shape to the code you wrote in Step 1.

1. Right-click the shape you just created.
2. Select Assign Macro… from the context menu.
3. Select ShowUserForm from the list.
4. Click OK.

Now, when you click the shape, your UserForm will pop up.

Summary

• Code: Use UserFormName.Show in a standard module.
• UI: Use Excel Shapes as buttons for a professional look.
• Link: Connect them using the “Assign Macro” feature.

This simple setup allows users to access your VBA tools without needing to open the developer tab or the VBE.