Excel UserForm Command Button: Setup and VBA Guide

An Excel UserForm command button is a clickable control that runs VBA when the user selects it. Add it from the VBA editor’s Toolbox, set its Caption and Name in the Properties window, then write the action in its Click event; a button is useful for choices such as submitting, clearing, or closing a form.

What a UserForm command button does

A UserForm is a custom dialog window built with VBA, and a CommandButton is one of the controls you can place on it. It gives the person using the workbook a visible way to request an action without needing to know a macro name or use a keyboard shortcut. For example, an order-entry form might have text boxes for a customer and quantity, plus buttons labeled Save and Cancel.

The button itself does not automatically save data or perform a calculation. Its Click event contains the VBA instructions that define what happens. Those instructions might check whether required fields are filled in, transfer values to a worksheet, display a message, or close the form. A button can also call a separate procedure, which is useful when the same operation is needed in more than one place.

How it differs from worksheet buttons

A UserForm CommandButton belongs to a form and is normally visible only while that form is open. It is not the same thing as a button placed directly on a worksheet. In Excel for Microsoft 365 on Windows, worksheet controls may be Form Controls or ActiveX controls, each with its own setup and event model. A UserForm button uses the Microsoft Forms control library and its event procedures in the VBA project.

This distinction matters when following instructions online: a worksheet Form Control button is commonly assigned a macro, while a UserForm button usually gets a procedure such as cmdSave_Click behind the form. The button’s Name determines the event-procedure prefix; its Caption is the text displayed to the user. Changing the Caption to “Save” does not rename the control or change the VBA event name.

How to add a command button

In desktop Excel for Windows, open the VBA editor with Alt+F11. In the editor, choose Insert > UserForm to create a form. If the Toolbox is not visible, choose View > Toolbox. Select the CommandButton control, then drag on the form to set its size and position. Select the button and press F4 to show its Properties window.

Set (Name) to a useful identifier such as cmdSave. Names should be distinctive and should not contain spaces.
Set Caption to the wording users will see, such as Save or Close.
Double-click the button while designing the form. The editor opens its Click event procedure, where you can add the VBA that should run when it is clicked.
To test the form, run it from the VBA editor or call its Show method from a standard module.

For example, a form named frmEntry could contain a button named cmdSave. Those names make the form and control easier to identify than the defaults UserForm1 and CommandButton1, especially after a project grows. The instructions above apply to desktop Excel; Excel for the web cannot create or run VBA UserForms. Excel for Mac has VBA support, but control and platform compatibility can differ, so check the target Mac version and test the actual form there.

Useful button properties

The Properties window controls the button’s appearance and behavior. Caption is the visible label, while (Name) is the identifier used in code. Keep captions short and action-focused. For a button labeled “Submit,” its Name might be cmdSubmit; the two values serve different purposes.

Other properties can help with usability. Enabled determines whether the user can click the button; setting it to False can prevent submission until the form is ready. Visible controls whether it appears. Default, when available for the form’s button setup, makes the button the form’s default action for Enter; Cancel can make a button respond to Esc. Use those behaviors carefully, since pressing Enter or Esc should not unexpectedly discard information. The ControlTipText property can provide a short hover hint, and TabIndex helps determine the order in which Tab moves through controls.

Writing the Click event

Here is a simple example for a form that contains a TextBox named txtName and a CommandButton named cmdSave. The event checks for an empty entry, places focus back in the text box if needed, and otherwise shows a confirmation before unloading the form.

Private Sub cmdSave_Click()
    Dim enteredName As String

    ' Read and trim the text entered on the form.
    enteredName = Trim$(Me.txtName.Value)

    If Len(enteredName) = 0 Then
        MsgBox "Enter a name before continuing.", vbExclamation, "Name required"
        Me.txtName.SetFocus
        Exit Sub
    End If

    MsgBox "Hello, " & enteredName & ".", vbInformation, "Saved"
    Unload Me
End Sub

Place this procedure in the code module for the UserForm, not in a worksheet module or a standard module. The object and control names in the code must match the names shown in the Properties window. Me refers to the form whose event is running. Unload Me closes that form and removes its loaded instance; if your form should remain open, omit that line or use Me.Hide instead.

This example only displays a confirmation; it does not write anything to a worksheet. For a real Save button, add the intended data-transfer logic after validating the entries. Keeping validation near the start of the event prevents incomplete values from being processed. For larger tasks, put the save operation in a separate, clearly named procedure and call it from the Click event rather than making one event procedure difficult to maintain.

Showing and closing the form

A form needs to be shown before the user can click its controls. You can run a public procedure in a standard module, or call the form from another macro. Replace frmEntry below with the actual form name.

Public Sub ShowEntryForm()
    ' Display the entry form before the user interacts with it.
    frmEntry.Show vbModal
End Sub

A modal form, as shown, keeps the user focused on that form until it is hidden or unloaded. With vbModeless, the user can interact with other Excel windows while the form remains open. Modeless forms can be convenient for repeated data entry, but code should account for the possibility that worksheet values change while the form is visible. A form shown without an explicit style is modal by default.

Use Unload Me when the form should be closed and its instance released. Use Me.Hide when you want to dismiss it temporarily and retain its current control values for later use. A separate Cancel button can simply hide or unload the form, depending on whether those entries should be kept. Make the difference clear to users: “Cancel” generally suggests that pending changes will not be submitted.

Design and accessibility considerations

Button wording should describe the result of clicking, not the internal macro name. “Add item,” “Search,” and “Close” communicate more than “Run” or “Button 1.” Use consistent labels and avoid putting several unrelated actions behind one control. A confirmation prompt may be appropriate before an irreversible action, but routine actions should not be burdened with unnecessary prompts.

Arrange controls in a logical reading and tab order. Users who navigate by keyboard should be able to reach the button without relying on a mouse; check TabStop and TabIndex along with the order of the other controls. An accelerator key can provide an Alt-key route when configured, but choose one that does not conflict with another control. Test the form at the display scaling and window size likely to be used, because long captions or crowded layouts can make controls hard to read.

Common problems and fixes

The Click event does not run: Confirm that the code is in the form’s code module and that the procedure name matches the control’s current Name. If the button was renamed after code was written, use the editor’s object and procedure lists to select the correct Click event.
The form does not open: Check that the macro calling Show is in the workbook’s VBA project and that the form name is correct. Excel for the web does not run VBA, so open the workbook in desktop Excel.
The code cannot find a control: Check spelling and capitalization in the control’s Name property. A control’s displayed Caption is not its code identifier, and a TextBox named TextBox1 will not respond to code referring to txtName.
The macro is blocked: A workbook containing VBA should be saved in a macro-enabled format such as .xlsm. Excel’s macro security settings may also prevent code from running; only enable macros for files you trust and follow your organization’s security policy.
The form behaves differently on another computer: Confirm the Excel platform and version, any referenced libraries, and the controls used. Test on the actual target environment rather than assuming that every Windows UserForm will behave identically in Excel for Mac.

Before running a macro that changes workbook data, save a copy of the workbook. VBA actions cannot generally be reversed with Ctrl+Z, so a backup is the safest way to recover from an unintended result.

When to use another approach

A UserForm is a good fit when users need a guided interface with several related fields, validation, and buttons. It can reduce accidental edits to a worksheet and make a repeated process more approachable. It is not automatically the best choice for a small task: a worksheet cell with data validation, a built-in Excel command, or a simple macro launched from the Quick Access Toolbar may be easier to maintain.

If users need a visible control directly on a worksheet, consider a worksheet Form Control button assigned to a macro rather than building a UserForm solely to provide one button. Avoid assuming that an ActiveX worksheet control is interchangeable with a UserForm control, particularly in cross-platform workbooks. Choose the simplest interface that works on the Excel versions and devices your users actually have.

Frequently Asked Questions

What is the difference between a button’s Name and Caption?

The Name is the identifier used by VBA, such as cmdSave, and it is part of the Click event’s procedure name. The Caption is the text displayed on the button, such as “Save.” Changing one does not automatically change the other.

How do I make a command button close a UserForm?

Put Unload Me in the button’s Click event to close and unload the form. Use Me.Hide if you want to hide it while retaining its current values in the loaded form instance.

Can a UserForm button run without VBA?

No. A UserForm CommandButton relies on VBA event code to perform its action. If macros are unavailable or disabled, the form’s button cannot carry out that VBA behavior.

Why does Excel for the web not show or run my UserForm?

Excel for the web does not support running VBA macros or displaying VBA UserForms. Open the macro-enabled workbook in a compatible desktop version of Excel to use the form.

Can I change the button text after adding it?

Yes. Select the button in the VBA editor and edit its Caption property in the Properties window. This changes the displayed wording, not the control’s Name or its existing event code.

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.