To insert a command button in an Excel VBA UserForm, open the Visual Basic Editor, choose Insert > UserForm, then open View > Toolbox and select CommandButton. Click the form to place it, set its Caption and (Name) in the Properties window, and double-click it to write its Click event code.
What a UserForm command button does
A UserForm is a custom dialog window built with VBA. A CommandButton gives the person using that form an action to take, such as submitting information, clearing fields, opening another form, or closing the dialog. Its visible label comes from the Caption property; its internal identifier comes from the (Name) property. Keeping those two roles distinct makes the form easier to maintain: a button can display “Save” while VBA refers to it as cmdSave.
In Excel for Microsoft 365 on Windows, you create and edit UserForms in the desktop Visual Basic Editor (VBE). VBA and UserForms do not run in Excel for the web. Excel for Mac has VBA support, but the VBE interface and available controls can differ from Windows; test a form on the platforms where it will be used, particularly if it relies on Windows-specific controls or APIs.
Insert a button in the UserForm designer
- Save a copy of the workbook, then save the working file as an Excel Macro-Enabled Workbook (
.xlsm) if it is not already macro-enabled. Macros cannot be undone with Ctrl+Z. - In Excel, open Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon and check Developer.
- In the VBE, choose Insert > UserForm. The form designer opens. If the Toolbox is not visible, choose View > Toolbox.
- Select the CommandButton control in the Toolbox, then click or drag on the form to place it. Dragging lets you set an approximate size immediately.
- Select the button and edit its settings in the Properties window. Change (Name) to an informative identifier such as
cmdSubmit, and Caption to the text people should see, such asSubmit. - Double-click the button to open its Click event procedure, add the action, and run the form to check the result.
The VBE shortcut on Windows is Alt+F11. On a Mac, Option+F11 may open the editor; some keyboards also require Fn. If the shortcut does not work, use the Excel menu or ribbon because keyboard behavior can depend on the keyboard and macOS settings.
Set useful button properties
Use short, action-oriented captions so users can tell what each button will do. “Add item” is clearer than “OK” when the action is not simply confirming a dialog. Give controls consistent names, for example cmdAdd, cmdCancel, and cmdClear. A name beginning with a control-type prefix is a convention, not a VBA requirement, but it helps identify controls in code.
Common properties include:
- Caption: The label displayed on the button. This can include spaces, even though the control’s (Name) cannot.
- (Name): The identifier used in VBA event procedures. Use a unique, readable name without spaces.
- Enabled: Set to False when an action should temporarily be unavailable; users then cannot activate the button.
- Visible: Set to False to hide a control. Hiding is different from disabling: a hidden button is not displayed.
- Default and Cancel: These can make a button respond to Enter or Esc while the form is active. Set them deliberately and test them alongside text boxes and other controls.
- Accelerator: Assigns a keyboard access key to the caption. Choose access keys that are distinct and make sense for the form.
Property names and the layout of the Properties window are visible in the designer. Not every property is appropriate for every form. In particular, do not make a destructive action such as deleting records the default Enter action merely because that behavior is convenient to configure.
Write the button’s Click event
Each button placed in the designer can have an event procedure in the UserForm’s code module. For a button named cmdSubmit, the event is normally named cmdSubmit_Click. Double-clicking the control creates or opens that procedure. The following example assumes the form also has a TextBox named txtName:
Private Sub cmdSubmit_Click()
Dim userName As String
' Read and trim the text entered in the form.
userName = Trim$(Me.txtName.Value)
If Len(userName) = 0 Then
MsgBox "Enter a name before submitting.", vbExclamation
Me.txtName.SetFocus
Exit Sub
End If
MsgBox "Submitted for " & userName, vbInformation
End Sub
This example validates that the text box is not blank and then displays a confirmation. It does not write anything to a worksheet; that behavior would need to be added to the procedure. The Me keyword refers to the form containing the code, which makes references to its controls clear. A real submission routine should also decide where data belongs, what to do if a cell is protected, and how to handle invalid or duplicate entries.
For a simple cancel button named cmdCancel, its Click event can hide or unload the form. Me.Hide closes the window visually but leaves the form loaded, which can be useful if code needs to read its values afterward. Unload Me removes the form instance from memory; use it when the form is finished and its values are no longer needed. Pick one based on how the calling macro uses the form rather than treating the two commands as interchangeable.
Add a command button while the form runs
It is also possible to create a button in code instead of placing it in the designer. This is useful when the form’s layout depends on information available only at runtime, such as a variable number of categories. The following code belongs in the UserForm module and creates one button near the top-left of the form:
Private Sub UserForm_Initialize()
Dim btn As MSForms.CommandButton
' Add a button to this form when it is initialized.
Set btn = Me.Controls.Add("Forms.CommandButton.1", "cmdDynamic", True)
With btn
.Caption = "Continue"
.Left = 18
.Top = 18
.Width = 90
.Height = 28
End With
End Sub
The control’s name must not conflict with another control on the form. Its position and dimensions use points, and the measurements in the example are starting values rather than a guarantee that the layout will suit every form. Set the form’s size and the control’s position together, and check that the button remains visible at the display scaling and window size users will use.
A key distinction is that creating a control at runtime does not automatically give it a normal, named Click procedure in the form module. The designer-generated event pattern works for controls known at design time. For runtime controls, use a class module with a WithEvents variable to receive events, and keep a reference to each handler for as long as the form needs it. Without that event-handling setup, the button may appear but clicking it will not run the action you expect.
Handle clicks on runtime-created buttons
For a single dynamic button, add a class module in the VBE through Insert > Class Module and name it CButtonHandler in the Properties window. Put this event code in that class module:
Public WithEvents Button As MSForms.CommandButton
Private Sub Button_Click()
MsgBox "The runtime-created button was clicked."
End Sub
Then add the following declarations and procedures to the UserForm module. The collection keeps the handler object alive; without a retained reference, event handling can stop when the handler goes out of scope.
Private mButtonHandlers As Collection
Private Sub UserForm_Initialize()
Dim btn As MSForms.CommandButton
Dim handler As CButtonHandler
Set mButtonHandlers = New Collection
Set btn = Me.Controls.Add("Forms.CommandButton.1", "cmdDynamic", True)
With btn
.Caption = "Continue"
.Left = 18
.Top = 18
.Width = 90
.Height = 28
End With
Set handler = New CButtonHandler
Set handler.Button = btn
mButtonHandlers.Add handler, btn.Name
End Sub
For several runtime buttons, create and retain one handler per button, and give each a unique name. You can extend the handler class to identify which button raised the event, then route that action to the appropriate routine. This pattern takes more setup than a designer button, so use it only when the controls really need to be created dynamically.
Choose between designer and runtime buttons
For a fixed form, inserting controls in the designer is usually the simplest option. The layout is visible while you build it, event procedures are straightforward, and another person can inspect the form without first running code. The designer is a good fit for ordinary Save, Cancel, Search, or Clear actions.
Runtime controls suit cases where the number or type of buttons changes with data or configuration. They can reduce the need to create many pre-positioned controls, but add responsibilities: code must build the layout, assign unique names, connect event handlers, and manage references. If the number of choices varies, consider whether a ListBox or ComboBox plus one fixed button would be clearer than generating a large set of buttons.
A UserForm button is also different from a button placed directly on a worksheet. A worksheet Form Control or shape can launch a macro without opening a custom dialog, while a UserForm button belongs to a dialog and can respond to the form’s text boxes, lists, and other controls. ActiveX worksheet controls are a separate option with different behavior and platform constraints. Choose based on where the user should interact, not just on which button looks familiar.
Common problems and fixes
- The button is visible but has no effect: Check that the control’s (Name) matches the event procedure prefix, and that the code is in the correct UserForm module. A button created at runtime needs a
WithEventshandler pattern. - The code does not compile: Confirm that the event procedure name is spelled correctly and that the control exists on that form. Use the VBE’s dropdowns above the code window to select the control and its Click event rather than typing the event name from memory.
- The form opens, but the button is missing: Check that initialization code runs, that the control’s coordinates fit inside the form, and that its Visible property is True. Avoid reusing a control name already present on the form.
- The wrong button is being referenced: Distinguish Caption from (Name). VBA code uses the name, not the visible label, so changing Caption does not rename the control.
- The macro cannot be run: Save as
.xlsmand use Excel’s macro security controls appropriately. Do not enable all macros globally; only run code from sources you trust. - The form behaves differently on another computer: Check Excel platform and version, screen scaling, references, and any platform-specific dependencies. Excel for the web cannot execute VBA macros.
Use the VBE’s Run command or the project’s normal launch macro to test the complete form, not just the button procedure in isolation. Check both expected input and empty or malformed input. For forms that change worksheet data, test on a copy first and consider how the code should respond if the target sheet, workbook, or destination cells are unavailable.
Frequently Asked Questions
Can I add a command button without the Toolbox?
Yes. You can create one with Controls.Add in UserForm code, but you must set its properties and arrange event handling yourself. For a fixed layout, the Toolbox is usually easier.
Why does double-clicking the button not create a working macro?
Double-clicking opens the Click event procedure; it does not decide what the button should do. Add VBA statements inside that procedure, and confirm its name matches the control’s (Name) property.
Can I rename the button’s displayed text?
Yes. Select it in the designer and edit Caption in the Properties window, or set its Caption in code. Changing Caption does not change the name used by event procedures.
Can a UserForm button write data to a worksheet?
Yes. Its Click procedure can validate form values and then write them to a worksheet, provided the code identifies the correct workbook, sheet, and destination. Add checks for missing or invalid input instead of assuming every field is filled in.
Can I use these buttons in Excel for the web?
No. Excel for the web does not run VBA macros or open VBA UserForms. Users need desktop Excel with macros permitted to use a VBA-based form.
Related
- Hide a Column in Power BI: Table Visuals and Models
- Conditional Formatting Greater Than: Excel Rules Explained
- Scatter Plot Data Sheet: How to Set Up and Read One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Declaration: Setup, Methods, and ExamplesTo declare a VBA Dictionary in Excel for Microsoft 365 on Windows, use Dim dict As Object followed…
- Google Sheets Macros: How to Create, Run, and Edit ThemYes. You can create macros in Google Sheets using its built-in recorder: in a desktop web browser, choose…
- VBA InputBox Yes/No Questions: Use MsgBox CorrectlyVBA’s standard InputBox cannot display Yes and No buttons; it collects text instead. To ask a true yes-or-no…
- Excel VBA Worksheet Attributes: Properties and ExamplesIn Excel VBA, “worksheet attributes” usually means the properties of a Worksheet object: values that describe or control…
- Excel VBA Join Array: Syntax, Examples, and Common FixesIn Excel VBA, use Join(array, delimiter) to combine the elements of a one-dimensional array into a single string.…
- Tiny Macro Recorder: What TinyTask Can and Can’t DoIf you mean TinyTask, it is a small Windows macro recorder that captures mouse and keyboard actions and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.