VBA Open UserForm on Button Click: Code and Setup Guide

To open an Excel VBA UserForm when a worksheet button is clicked, create a public macro in a standard module and assign that macro to a Form Control button. For example, use ShowEntryForm as the button’s assigned macro; the code below creates and displays the form.

Public Sub ShowEntryForm()
    Dim entryForm As UserForm1

    ' Create and display a fresh form instance.
    Set entryForm = New UserForm1
    entryForm.Show vbModal
End Sub

Replace UserForm1 with the form’s actual name. This example opens the form modally, so the user must close or hide it before returning to the worksheet. The steps below explain how to create the form, connect different kinds of buttons, choose how the form behaves, and troubleshoot common problems.

How a worksheet button opens a UserForm

A worksheet button does not display a UserForm by itself. Its job is to run a macro, and that macro tells Excel to show a form. This separation is useful: the button handles the click, while the macro handles the action. It also means you can use the same macro for a button, a shape, or another supported way of starting a macro.

A UserForm is a custom window built in the Visual Basic Editor (VBE). It can contain controls such as text boxes, labels, list boxes, and command buttons. For example, a data-entry form might collect a person’s name and department, then write the information to a worksheet. The form’s name in the VBE is its VBA class name; it is not necessarily the same as the caption displayed in its title bar.

Create and name the UserForm

Open the workbook in desktop Excel and open the VBE. In Excel for Windows, press Alt+F11 or select Developer > Visual Basic. On a Mac, use Developer > Visual Basic; depending on the keyboard, you may need Fn with the function-key shortcut.
In the VBE, choose Insert > UserForm. Excel adds a form to the project and normally gives it a default name such as UserForm1.
Click the form and use the Properties window to set its (Name) property, for example to frmEntry. Set its Caption property separately if you want a reader-friendly title such as Entry Form.
Add controls from the Toolbox if the form needs them. Give controls clear names, such as txtName for a text box or cmdCancel for a command button. The names are what your VBA refers to; changing a control’s visible caption does not change its name.

If you rename the form to frmEntry, update the example macro to declare and create frmEntry instead of UserForm1. The name used in code must match the form’s (Name) property exactly.

Add the macro in a standard module

In the VBE, select Insert > Module to create a standard module, then paste the opening macro into that module. A standard module is the usual place for a worksheet button’s assigned macro. Keep the procedure public and give it no arguments: Excel’s Assign Macro dialog expects a callable macro, not a UserForm event procedure that requires parameters.

In the example, New UserForm1 creates a fresh form object, and entryForm.Show vbModal displays it. The local variable keeps a reference to that instance while the modal form is open. If the form is named frmEntry, the matching declaration and creation lines are Dim entryForm As frmEntry and Set entryForm = New frmEntry. Do not paste the macro into a worksheet’s code module or the UserForm’s code module if you intend to assign it directly as a worksheet macro.

Connect a Form Control button to the macro

For a straightforward worksheet button, a Form Control is generally the simplest choice. In Excel for Windows, show the Developer tab if it is hidden by using File > Options > Customize Ribbon and selecting Developer. Then follow these steps:

Select Developer > Insert and choose Button under Form Controls.
Drag on the worksheet to draw the button. Excel opens the Assign Macro dialog.
Select ShowEntryForm and choose OK. If the dialog does not appear, right-click the button and select Assign Macro.
Right-click the button and choose Edit Text to change its displayed label, for example to Open Entry Form.
Click an empty worksheet area to finish editing, then click the button to run the macro.

If the macro is not listed, check that it is in a standard module, is a public Sub, has no required arguments, and belongs to the workbook that contains the button. A worksheet shape can also run a macro: insert or select the shape, right-click it, choose Assign Macro, and select the same procedure. This can be convenient when you want a styled graphic rather than a standard button.

Using an ActiveX command button instead

An ActiveX Command Button uses a different connection. In Excel for Windows, select Developer > Insert and choose Command Button under ActiveX Controls. Turn on Design Mode, double-click the control, and Excel opens its click-event procedure in that worksheet’s code module. The event can show the form directly:

Private Sub CommandButton1_Click()
    ' Display the form when this worksheet control is clicked.
    UserForm1.Show vbModal
End Sub

Use the actual control name and form name from your project. This code is an event handler, so it belongs in the worksheet code module created for that ActiveX control; it is not the same kind of procedure you select in Assign Macro. Turn off Design Mode before clicking the control to use it. ActiveX controls are Windows-specific and are not supported in Excel for Mac, so they are a poor choice for a workbook that must work across Windows and Mac. A Form Control or an assigned shape is usually more portable.

Choose modal or modeless behavior

The display mode determines whether the user can work in the worksheet while the form remains open. vbModal makes the form modal: the user must dismiss it before interacting with Excel behind it. This is often appropriate for a required data-entry step, a confirmation, or a small dialog. In the first example, the modal setting is explicit; UserForm1.Show without an argument also opens a form modally by default.

vbModeless allows the user to switch back to the worksheet while the form stays visible. It can suit a helper panel or a form used alongside worksheet data. With a modeless form, take care not to create duplicate windows every time the launch button is clicked. A simple version uses the default instance:

Public Sub ShowEntryFormModeless()
    ' Keep the default form instance available while it is open.
    UserForm1.Show vbModeless
End Sub

Assign this procedure to a button just as you would the modal macro. Modeless forms require a little more care if they read or write worksheet cells, because the user can edit the sheet while the form is open. Decide whether the form should refresh its values, validate the worksheet again when saving, or prevent conflicting edits.

Initialize the form and close it safely

A form’s Initialize event is a useful place to set up controls when a new instance is created. For instance, you might clear text boxes or populate a list box from a worksheet. Because this event runs during form initialization, do not assume that a worksheet value is valid without checking it. If you need to retain values when a user temporarily dismisses a form, distinguish between hiding the form and unloading it.

A Cancel button on the form can hide or unload the current instance. Put its click event in the UserForm’s own code module:

Private Sub cmdCancel_Click()
    ' Close this form and release its loaded instance.
    Unload Me
End Sub

Unload Me closes and unloads the form; when a new instance is created later, its controls are initialized again. Me.Hide makes the form invisible but keeps that instance loaded, so its control values can remain available. Choose based on the intended behavior rather than treating the two statements as interchangeable. If the form collects information, decide where and when to validate and save it before the user closes the window.

Common problems and practical checks

The button appears to do nothing: Confirm that macros are enabled, the workbook is open in desktop Excel, and the button is assigned to the intended macro. For an ActiveX button, check that Design Mode is off.
The macro is missing from Assign Macro: Move it to a standard module and confirm that it is a public Sub with no required arguments. A Private procedure or a UserForm event such as cmdCancel_Click is not the right target for a Form Control button.
Excel reports that a name is not defined: Check the form’s (Name) property and match it in the code. The form caption shown to users is a different property.
The form opens more than once: Check whether repeated clicks create multiple instances, particularly with modeless forms. Consider using a default instance or explicitly managing whether an instance is already open.
The button is being edited rather than clicked: Click away from the control to leave text or sizing mode. For an ActiveX control, leave Design Mode; for a Form Control, make sure you are not in Edit Text mode.
The macro works on one computer but not another: Confirm that the workbook is saved as a macro-enabled .xlsm file and that the other computer’s Excel settings permit the macro. Do not ask users to enable macros in files they do not trust.

VBA macros cannot be undone with Ctrl+Z. Save a copy of the workbook before running a new or changed macro, especially if the form writes to cells, clears ranges, or changes workbook data. Test with a copy and sample records first.

Excel versions, Mac, and web limitations

VBA UserForms and worksheet macros require desktop Excel. Excel for the web can open and edit many workbook contents, but it cannot run VBA macros or open a VBA UserForm. A button that depends on VBA therefore will not provide the same interaction in a browser; users need to open the workbook in the desktop application.

Excel for Mac supports VBA in desktop Excel, but some Windows-specific controls and features differ. In particular, ActiveX controls are not supported on Mac. Prefer Form Controls or shapes for worksheet launch buttons if both Windows and Mac users need to use the workbook, and verify the form and its controls on the actual Excel platforms you support. A file saved as .xlsm can store VBA, but the file extension does not bypass macro security settings.

Frequently Asked Questions

How do I open a UserForm from a button in Excel?
Create a public, argument-free macro in a standard module that calls the form’s Show method. Insert a Form Control button, assign the macro to it, and click the button in desktop Excel.

Can I open a UserForm without a worksheet button?
Yes. Run the macro from the VBE, assign it to a shape, or call it from another VBA procedure or event. A worksheet button is only one way to start the macro.

Why is my UserForm not listed in Assign Macro?
The form itself is not normally the macro to assign. Assign a public Sub in a standard module that shows the form. The procedure should have no required arguments; event handlers and private procedures will not appear as suitable button macros.

How do I make the form stay open while I use the worksheet?
Show it with vbModeless rather than vbModal. A modeless form lets the user work in Excel behind it, so account for worksheet edits that may happen while the form is still open.

Will a VBA UserForm button work in Excel for the web or Mac?
Excel for the web does not run VBA macros or UserForms. Desktop Excel for Mac supports VBA, but not ActiveX controls. For a workbook shared across desktop platforms, use a Form Control or shape and check the form’s controls on both systems.

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.