VBA: Open a UserForm When a Command Button Is Clicked

To open an Excel VBA UserForm when a worksheet button is clicked, assign a public macro to a Form Control button, or put a UserForm.Show statement in an ActiveX button’s Click event. For example, if your form is named frmEntry, the macro can show it with frmEntry.Show; the exact setup depends on which kind of button you inserted.

Choose the right command button

Excel offers two different worksheet button types, and their click behavior is different. A Form Control button runs a macro that you assign to it. An ActiveX Command Button has a Click event where you write VBA. Both can open the same UserForm, but the code belongs in different places.

Form Control button: A straightforward choice for a button that simply runs a macro. The macro lives in a standard module and is selected through the Assign Macro dialog.
ActiveX Command Button: Useful when you need event code or properties such as a control name. Its event procedure lives in the worksheet’s code module. ActiveX controls are not supported in Excel for Mac.

For a simple Open Form action, a Form Control is often the easier option. If you already have an ActiveX button, or need to respond to its specific events, use its Click event instead. In either case, give your form a clear name in the VBA editor’s Properties window. The examples below use frmEntry; replace that name with the actual name of your form.

Set up a Form Control button

In Excel for Microsoft 365 on Windows, first make the Developer tab visible if it is missing: choose File > Options > Customize Ribbon, select Developer, and choose OK. Open the Developer tab, choose Insert, and select Button under Form Controls. Drag on the worksheet to place the button. When Excel opens Assign Macro, you can choose an existing macro or create one.

In the VBA editor, choose Insert > Module to create a standard module, if the workbook does not already have one.
Put a public, argument-free procedure in that module. A Form Control button can run a macro with this structure:

Public Sub OpenEntryForm()
    ' Show the form using its default instance.
    Dim entryForm As frmEntry
    Set entryForm = frmEntry
    entryForm.Show
End Sub

Return to the worksheet, right-click the button, choose Assign Macro, select OpenEntryForm, and choose OK.
Right-click the button and choose Edit Text if you want its label to say something like Open Entry Form.

Click the button outside of design or editing mode to run the macro. If you created the macro after placing the button, assign it using the same dialog. The macro should be in a standard module and should be Public with no parameters so it appears as an assignable macro.

Use an ActiveX button Click event

An ActiveX button does not use the Assign Macro dialog. On Windows, choose Developer > Insert > ActiveX Controls > Command Button, then draw the button. With Design Mode turned on, double-click the control. Excel opens the worksheet’s code module and creates a Click event procedure. Put the form-opening code inside that procedure:

Private Sub CommandButton1_Click()
    ' Show the form when this worksheet button is clicked.
    Dim entryForm As frmEntry
    Set entryForm = frmEntry
    entryForm.Show
End Sub

The control’s displayed name may not be CommandButton1. Excel uses the control’s name in the event procedure, and you can inspect or change that name in the Properties window while in Design Mode. Keep the event procedure in the worksheet module that owns the button; placing it in a standard module will not connect it to that control’s click event.

After entering the code, return to the worksheet and turn off Design Mode on the Developer tab. Design Mode lets you select or edit the control; it does not run the button’s Click event. With Design Mode off, click the button to open the form.

What Show does: modal and modeless forms

Calling Show displays the form. By default, a VBA UserForm opens modally: the user must close or hide it before interacting with the workbook window behind it. This is a sensible default for a short data-entry task where the user should complete or dismiss the form before continuing in the sheet.

If users need to keep the form open while selecting cells or working in Excel, show it modelessly by passing vbModeless:

Public Sub OpenEntryFormModeless()
    ' Allow the user to work in Excel while the form remains open.
    Dim entryForm As frmEntry
    Set entryForm = frmEntry
    entryForm.Show vbModeless
End Sub

A modeless form can remain open while the user changes the worksheet, so its code should not assume the active cell or selection stays the same. If an event on the form reads or writes worksheet cells, qualify the intended worksheet explicitly rather than relying on whichever sheet happens to be active. A modal form is usually easier to manage when the form depends on a particular worksheet state.

Form names, instances, and closing the form

The UserForm’s name is its VBA object name, not necessarily the text in its title bar. To check it, open the Visual Basic Editor, select the form in Project Explorer, and inspect the (Name) property. The form’s Caption property controls the title-bar text. If your project still calls the form UserForm1, either use that name in the code or rename it consistently; a mismatch causes a compile error such as Variable not defined.

The examples use the form’s default instance, which Excel makes available using the form’s name. This is convenient for a button that opens one shared form. If you deliberately create multiple instances of the same form, store each instance in an object variable with a lifetime long enough for the form to remain available. That design is useful for advanced workflows but is unnecessary for a basic open-on-click button.

To dismiss a form from one of its own buttons, code can call Unload Me to unload that instance. Hiding a form instead leaves it loaded and preserves its current control values; unloading it removes the loaded instance, so its values should not be assumed to persist. Choose based on whether the next opening should reuse the current entries or start with a fresh form.

Common problems and practical fixes

The form name is wrong: Compare the name in the code with the form’s (Name) property, not its caption. Correct the spelling or update the code.
The Form Control does nothing: Right-click it and check Assign Macro. Confirm that the chosen macro is in a standard module, is Public, and has no arguments.
The ActiveX button edits instead of opening the form: Turn off Developer > Design Mode, then click the button again. Design Mode is for selecting and editing ActiveX controls.
The event code is in the wrong module: An ActiveX button’s Click procedure belongs in the code module for the worksheet containing that button. A Form Control macro belongs in a standard module.
The macro is blocked or missing: Save the workbook as a macro-enabled .xlsm file and check Excel’s security notification. Only enable macros for files from a source you trust; do not lower security settings just to make an unfamiliar workbook run.

When diagnosing an error, open the Visual Basic Editor and use Debug > Compile VBAProject. A compile error can reveal a misspelled form name or a code issue before you try clicking the button again. If Excel highlights a line, check the object name and the module in which the code is stored.

Save, platform, and macro-safety details

VBA macros and UserForms do not run in Excel for the web. A workbook can be opened there, but the form cannot be launched by a worksheet button; use the desktop Excel application for this VBA workflow. Excel for Mac supports VBA, but it does not support ActiveX controls. A Form Control and its assigned macro are the more suitable button route on Mac, though ribbon placement and available controls can vary by Excel release.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the VBA project is retained. Before running a macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a macro that changes cells or workbook content may not be reversible through Excel’s normal Undo command. This particular opening macro only displays a form, but the form’s own event code may make changes when its buttons are used.

Frequently Asked Questions

How do I open a UserForm with a worksheet button?
For a Form Control button, assign it a public macro in a standard module that calls frmEntry.Show, using your form’s actual name. For an ActiveX button, put the Show call in that button’s worksheet Click event.

Why does my Form Control button not appear in Assign Macro?
The procedure may be Private, have arguments, or be stored in a sheet or UserForm module rather than a standard module. Use a Public, argument-free Sub in a standard module, then reopen Assign Macro.

Should I use a Form Control or ActiveX button?
For a basic button that runs one macro, a Form Control is usually simpler. ActiveX gives you a control-specific event procedure and properties, but it is Windows-only in Excel and can require Design Mode for editing.

How can I keep the form open while using the worksheet?
Call Show vbModeless instead of plain Show. A modeless form does not block interaction with Excel, so make worksheet references explicit if the user might switch sheets.

Can I open a VBA UserForm in Excel for the web?
No. Excel for the web does not run VBA macros or display VBA UserForms. Open the macro-enabled workbook in desktop Excel to use the button and form.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.