Assign a Macro to a UserForm Command Button in Excel

In Excel for Microsoft 365 on Windows, you do not assign a UserForm command button through the worksheet’s Assign Macro dialog. Instead, add code to the button’s Click event in the UserForm’s code module, and have that event run the procedure you want.

What “assign a macro” means for a UserForm

A UserForm is a custom VBA window, and its command button is a control inside that window. The button responds to events, such as being clicked; its event procedure is where you write or call the VBA that should run. This is different from a Forms button or a shape placed on a worksheet, which can be linked to a macro using an Assign Macro command.

For example, a button named CommandButton1 on a form named UserForm1 has a click event procedure named CommandButton1_Click. The procedure belongs in the code module for that form, not in a worksheet module or a standard module. It can contain the task itself or call a reusable public procedure in a standard module.

That distinction matters because looking for “Assign Macro” after right-clicking a UserForm button leads to a dead end: the UserForm designer does not use that mechanism. The event connection is made by the button’s name and the matching event procedure.

Add a command button to the UserForm

In desktop Excel, open the Visual Basic Editor (VBE) with Alt+F11 on Windows. In the editor, choose Insert > UserForm if you need a new form. The Toolbox normally appears with the form; choose CommandButton and click or drag on the form to place it. If the Toolbox is hidden, show it from the VBE’s View menu.

Select the new control and open the Properties window with F4. The (Name) property is the identifier VBA uses, while Caption is the text displayed on the button. For a clear example, leave the name as CommandButton1 and set Caption to Record today. Changing the caption does not change the event procedure name; changing the Name does.

It is worth choosing descriptive names when a form has several controls. For instance, a button named cmdSave produces a cmdSave_Click event, which is easier to recognize than a series of numbered controls. If you rename a control, create or select the event for its new name in the VBE rather than assuming the old event will still run.

Write the button’s Click event

Double-click the button in the UserForm designer to open its code module. The VBE should create a procedure for the button’s default event, usually Click. You can also select the button in the code window’s left dropdown and choose Click from the right dropdown. Put the action between the procedure’s opening and closing lines.

For a small task, the event procedure can contain the action directly. For logic that should also be usable from another button, a worksheet, or another macro, put the main task in a public procedure in a standard module and call it from the event. The latter keeps the button’s job simple: respond to the click and pass control to the work routine.

Here is a complete example. It assumes the workbook has a sheet named Sheet1. The standard-module function writes the current date to cell B2 and reports an error if that sheet or cell operation is unavailable. The UserForm event calls the function and displays a success message only when it returns True.

' Standard module: choose Insert > Module in the VBE.
Public Function RecordToday() As Boolean
    Dim targetSheet As Worksheet

    ' Write today's date to the example destination.
    On Error GoTo HandleError
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    targetSheet.Range("B2").Value = Date
    RecordToday = True
    Exit Function

HandleError:
    MsgBox "Could not update Sheet1!B2: " & Err.Description, vbExclamation
    RecordToday = False
End Function

In the UserForm’s code module, use the matching button event:

Private Sub CommandButton1_Click()
    Dim completed As Boolean

    ' Run the task and confirm success.
    completed = RecordToday()
    If completed Then
        MsgBox "Today's date was written to Sheet1!B2.", vbInformation
    End If
End Sub

Before running the example, save a copy of the workbook. A macro’s changes cannot be reversed with Ctrl+Z. Once the code is in place, return to the form designer, run the form, and click Record today. If the sheet is named differently, update the string in the code to match its tab name exactly.

Show the form from a macro

A button inside a UserForm cannot help users open that form before it is displayed. To launch the form, create a separate public procedure in a standard module. This is also useful if you want to connect the launch procedure to a worksheet button, a shape, or a keyboard shortcut.

Public Sub ShowEntryForm()
    Dim entryForm As UserForm1

    ' Create and display the form.
    Set entryForm = New UserForm1
    entryForm.Show
End Sub

The example assumes the form’s (Name) property is UserForm1. Replace that identifier if you renamed the form. Run ShowEntryForm from the VBE or connect it to a worksheet control. By default, .Show displays a modal form: the user must close it before working in the Excel window behind it. A modeless form is possible with entryForm.Show vbModeless, but consider whether users should be able to edit the workbook while the form remains open.

Choose the right kind of button

Excel has several controls that can look like buttons, but they do not all connect to code in the same way. Choose based on where the control lives and how much interface you need:

UserForm CommandButton: Use its Click event when the button is part of a custom VBA dialog with text boxes, lists, or other controls.
Forms button or worksheet shape: Use Assign Macro when a simple click target on a worksheet should run a public macro. A shape can be formatted more freely; a Forms button is a traditional form control.
ActiveX command button on a worksheet: Use its worksheet event procedure in the relevant sheet module, not the UserForm event procedure. ActiveX worksheet controls are a Windows-specific choice and are not supported in Excel for Mac in the same way.

For a single action on a worksheet, a shape assigned to a macro is often simpler than building a form. A UserForm is more suitable when a task needs input fields, validation, choices, or several coordinated actions. Do not confuse an ActiveX command button on a worksheet with a CommandButton placed inside a UserForm: similar names do not mean identical setup steps.

Common problems and how to fix them

Clicking the button does nothing: Check that the code is in the UserForm’s module and that the procedure name matches the control’s current name and the Click event. A procedure placed in a standard module with a similar name is not automatically the button’s event handler.
The macro name is not recognized: If the event calls a procedure in a standard module, make sure the procedure is declared Public and the module is a standard module. Check spelling and confirm that the called procedure is not a private procedure in another module.
The form opens but the button is missing: You may be running a different form instance or have changed its name. Confirm the form and control names in the Properties window, then run the intended launch procedure.
An error mentions a worksheet or range: Check the sheet tab name used in the code and confirm the destination cell is appropriate. In the example, Sheet1 is a worksheet tab name, not necessarily the sheet’s VBA code name.
The button’s displayed text changed, but the code did not: Caption controls the visible label; Name identifies the control in VBA. Set the procedure for the control’s Name, not its Caption.
The VBE stops on a line in yellow: Execution has paused at an error or breakpoint. Read the error message, verify names and references, and use the VBE’s Reset control before trying again after a runtime error.

Also check that macros are allowed to run for the file. An Excel workbook saved as .xlsx cannot retain VBA code; use a macro-enabled workbook format such as .xlsm when the workbook must keep its macros. If the code does not compile, use Debug > Compile VBAProject in the VBE to identify syntax or declaration problems before testing the form again.

Platform and security considerations

These steps describe desktop Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros or display VBA UserForms, so users need desktop Excel to use this interface. Excel for Mac includes VBA support, though some capabilities differ; the Windows Alt+F11 shortcut is commonly Option+F11 on Mac, and some keyboards require Fn as well. Menu access and available controls can vary by Mac version. Do not rely on worksheet ActiveX controls for a workbook intended for Mac users.

VBA can change workbook data, so only run macros from files and sources you trust. Save a backup before testing code that edits cells, deletes data, or changes workbook structure; Ctrl+Z does not undo a macro’s actions. Avoid lowering macro security globally just to make one file work. If a workbook is blocked or prompts about macros, follow your organization’s security guidance or use a trusted copy from its legitimate source.

Frequently Asked Questions

Can I assign a macro directly to a UserForm button?
No—not with the worksheet Assign Macro dialog. Put the desired action in the button’s Click event, or call a public macro from that event.

Where does the button code go?
It goes in the code module for the UserForm that contains the button. Double-click the button in the form designer to create or open its Click event.

Can a UserForm button run a macro in a standard module?
Yes. The event procedure can call a public Sub or Function in a standard module, provided the procedure name is correct and its scope allows the call.

How do I make a worksheet button open the UserForm?
Create a public launch procedure in a standard module, such as ShowEntryForm, and assign that procedure to a Forms button or worksheet shape. The launch macro displays the form; the form’s own buttons handle actions inside it.

Will the UserForm work in Excel for the web?
No. Excel for the web does not run VBA or UserForms. Open the macro-enabled workbook in desktop Excel to use the 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.