Call a Macro from an Excel UserForm Button

Yes. In Excel for Microsoft 365 on Windows, put a call to a Public Sub in the UserForm command button’s Click event procedure. The button then runs that procedure when a user clicks it; the macro usually belongs in a standard VBA module, while the click event belongs in the UserForm’s code module.

How a UserForm button runs VBA

A UserForm is a custom dialog built in the Visual Basic for Applications (VBA) editor. Its command button is a control with a name, a caption, and events. The Click event occurs when the user activates the button. VBA runs the event procedure associated with that control, and the procedure can call other VBA procedures.

This is different from assigning a macro to a button placed directly on a worksheet. Worksheet controls can have a macro assigned through a dialog, but a UserForm command button is connected through its event code. You do not need to right-click the UserForm button and use an “Assign Macro” command; that is not the usual way to connect a UserForm control.

Keeping the button event short and putting the work in a separate procedure makes the code easier to read and reuse. The event handles the user’s action; the macro performs the task. If another button or procedure needs the same task, it can call that same public procedure.

Create the UserForm and command button

In Windows Excel, save a copy of the workbook first, then open the Visual Basic Editor with Alt+F11 or choose Developer > Visual Basic. If the Developer tab is missing, enable it through File > Options > Customize Ribbon and select Developer. In the editor, choose Insert > UserForm, then use the Toolbox to add a CommandButton.

Select the button and use the Properties window to set its (Name) to cmdRunMacro and its Caption to something a user will understand, such as Run report. The name is used in VBA; the caption is the visible label. Avoid spaces in the control name. If the Properties window is not visible, press F4 while the control is selected.

In the Project Explorer, a double-click on the form opens its code module. Double-click the command button to create or open its click event. Confirm that the procedure name uses the control’s name followed by _Click. The event handler must be in the UserForm module, not in a worksheet module or a standard module.

Put the macro in a standard module

For a clear separation between interface and task, add a standard module using Insert > Module in the Visual Basic Editor. Put the procedure you want to run there and declare it Public. A basic example displays a message so you can confirm the connection without changing workbook data:

Option Explicit

Public Sub ShowRunConfirmation()
    ' Confirm that the button reached this procedure.
    MsgBox "The UserForm button called the macro.", vbInformation, "Button test"
End Sub

Now put the event procedure in the UserForm’s code module. In this example, the button’s (Name) property is cmdRunMacro:

Option Explicit

Private Sub cmdRunMacro_Click()
    ' Call the public procedure in the standard module.
    ShowRunConfirmation
End Sub

Clicking the button while the form is open should display the confirmation message. You can write the call as Call ShowRunConfirmation too, but the Call keyword is optional when no arguments are passed. If you do use Call with arguments, VBA requires parentheses around the argument list; without Call, the usual form is the procedure name followed by its arguments.

Call a macro that does useful work

Replace the demonstration procedure with the task you need, such as validating entries, preparing a report, or updating a selected range. For example, a UserForm might collect a name in a text box and pass it to a separate procedure. The procedure’s parameter should match the value being passed:

Option Explicit

Public Sub ShowGreeting(ByVal userName As String)
    ' Display the name received from the form.
    MsgBox "Hello, " & userName & ".", vbInformation, "Greeting"
End Sub

With a text box named txtName and a command button named cmdGreet, put this event in the UserForm module:

Option Explicit

Private Sub cmdGreet_Click()
    ' Pass the text box value to the public procedure.
    ShowGreeting Me.txtName.Value
End Sub

The Me keyword refers to the current UserForm, so the code reads that form’s text box. For real tasks, check that required fields contain valid values before calling the procedure. A blank name, an invalid date, or text where a number is expected can otherwise lead to an error or an unintended result. Keep validation and error messages understandable to the person using the form.

Open the form from the workbook

A UserForm must be shown before a user can click its button. One common setup is to create a separate public procedure in a standard module to display the form:

Option Explicit

Public Sub OpenReportForm()
    ' Display the form named frmReport.
    frmReport.Show
End Sub

Change frmReport to the actual name of your form. You can run OpenReportForm from the VBA editor, the Macros dialog, or a separate worksheet button assigned to that procedure. This is a separate step from the UserForm button’s click event: the opening procedure shows the form, and the event procedure responds to a click after the form is open.

To run a public, argument-free procedure from Excel, press Alt+F8 on Windows, select its name in the Macro dialog, and choose Run. The Mac equivalent for opening the Visual Basic Editor or Macro dialog can depend on the keyboard and Excel version; use the Developer tab or Excel’s macro commands if a function-key shortcut is intercepted by macOS. A UserForm button itself does not need to appear in the Macro dialog, because it is an event-driven control.

Common problems and how to fix them

The button does nothing. Check that the code is in the correct UserForm module and that the event name matches the button’s current (Name) property. A procedure named cmdRunMacro_Click will not respond to a button named CommandButton1.
“Sub or Function not defined” appears. Confirm that the called procedure exists, its spelling matches, and it is accessible. A procedure declared Private in another module cannot be called from the form; use Public Sub in a standard module for this pattern.
The code runs but reports an error. Step through it in the editor with F8 on Windows. Check assumptions about worksheet names, selected cells, input values, and workbook state. A procedure that relies on an active sheet can affect a different sheet if the user changes the selection, so qualify range references with the intended worksheet when appropriate.
The form opens, but the button is disabled or hidden. Inspect the button’s Enabled and Visible properties, and check whether other code changes them. Also make sure the form is not being shown modally in a way that makes the surrounding workbook seem unresponsive; a modal form keeps focus until it is closed.
The macro works in the editor but not after reopening the file. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). A regular .xlsx file does not retain VBA project code when saved in that format.

When debugging, place a breakpoint in the click event by clicking in its left margin, then click the button. If execution stops there, the event is connected and the issue is farther down the call chain. If it does not stop, check the control name, event procedure, and whether the form shown is the form whose code you edited.

Macro security and workbook format

Excel may block macros depending on the workbook’s source and the organization’s security policies. Do not respond by enabling all macros globally. On Windows, macro options are available through File > Options > Trust Center > Trust Center Settings > Macro Settings, although an organization can manage or restrict these settings. Only enable macros in files you trust, and follow your organization’s rules for approved files and locations.

Save the workbook as .xlsm to preserve its VBA code. A macro-enabled template uses .xltm. If you distribute the workbook, tell recipients what the form does and how to obtain the file safely. A digital signature can help identify a publisher when a VBA project is signed, but signing does not make unknown code safe. Keep a backup before changing a macro: VBA actions generally cannot be reversed with Excel’s Ctrl+Z undo command.

Excel for Mac and other button types

This guidance is for desktop Excel with VBA. Excel for Mac includes VBA and supports UserForms, but available controls, security settings, menus, keyboard shortcuts, and compatibility details can differ from Windows. Test the form in the specific Mac version and workbook environment where it will be used, especially if the project depends on Windows-only components or operating-system features. Excel for the web does not run VBA macros, so a UserForm-based workflow will not run there.

A UserForm CommandButton is also not the same thing as an ActiveX command button placed on a worksheet or a Form Control button. Those worksheet controls have their own setup and platform limitations. If the goal is a pop-up interface with several input fields, the UserForm event approach is appropriate. If the goal is simply to run a procedure from a worksheet, a worksheet button assigned to a public macro may be simpler.

When to use a direct call or Application.Run

For a known procedure in the same VBA project, call it directly by name, as in ShowRunConfirmation. This is the simplest option and makes it easier for VBA to identify a misspelled procedure during compilation. Use Application.Run only when you have a specific reason to invoke a procedure by name at runtime, such as selecting a macro name dynamically or calling a procedure in another workbook. It is not required just because the call starts from a UserForm.

Likewise, there is usually no reason to copy the entire macro into the button’s click event. A long event procedure mixes interface code and task logic, making it harder to reuse and maintain. A useful design is to let the click event validate or collect form values, call a procedure that performs the task, and then show a clear success or error message.

Frequently Asked Questions

Can a UserForm button run a macro in a standard module?
Yes. Make the procedure a Public Sub in a standard module, then call it by name from the button’s Click event procedure.

Do I need to assign a macro to a UserForm command button?
No. A UserForm command button runs its associated event procedure. Put the call to the macro in the button’s Click event rather than looking for the worksheet control’s Assign Macro workflow.

Why can’t my button find the macro?
Check the procedure name and spelling, confirm it is in the same VBA project, and make it public in a standard module if the form calls it from outside its own module. Also check for compile errors that prevent the project from running.

Can the button pass a value to the macro?
Yes. Give the called procedure a parameter, then pass a value from the form, such as Me.txtName.Value. Validate the input first if the procedure expects a particular type or a nonblank value.

Will this work in Excel for the web?
No. Excel for the web does not execute VBA macros or display VBA UserForms. Use desktop Excel for this workflow, or consider a different approach if browser-based use is required.

Can I undo what the macro changes?
Usually not with Excel’s normal Undo command. Save a backup before running code that changes workbook contents, and add your own recovery or confirmation steps when the macro’s actions could be consequential.

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.