[VBA] How to Call a Standard Module Macro from a UserForm Button

Introduction

When creating professional tools in VBA, you often use UserForms to build interactive interfaces. A common requirement is: “When I click a button on the form, I want to run the main processing logic written in a Standard Module.”

Separating the UserForm code (interface) from the main processing code (logic) makes your program easier to read and maintain.

This article explains the basic steps to call a macro located in a Standard Module when a CommandButton on a UserForm is clicked.

Overview and Steps

Here is the workflow for this tutorial:

1. Standard Module: Write the main macro (e.g., ProcessData).
2. UserForm: Create a form and add a “Run” button.
3. Form Code: Write the code to call ProcessData when the button is clicked.
4. Show Form: Write a separate macro to display the UserForm.

VBA Sample Code

Step 1: Create the Main Macro (Standard Module)

First, write the main logic in a Standard Module (e.g., Module1). In this example, we will simply input the current date and time into the active cell.

Standard Module: Module1

' Main processing logic called from the form
Sub ProcessData()
    ' Input current date and time into the active cell
    ActiveCell.Value = "Execution Time: " & Now()
    MsgBox "Processing complete."
End Sub

' Macro to display the UserForm
Sub ShowMainForm()
    ' Show MainForm in modeless mode
    MainForm.Show vbModeless
End Sub

Step 2: Create the UserForm

1. In the VBA Editor, select Insert > UserForm to create a new form.
2. In the Properties window, change the form’s (Name) to MainForm.
3. Drag and drop a CommandButton from the toolbox onto the form.
4. Set the button properties as follows:
   • (Name): ExecuteButton
   • Caption: Run Process

Step 3: Write the Button Click Event (UserForm)

Double-click the “Run Process” button you created to open the form’s code window. Write the code to call ProcessData from Module1.

UserForm: MainForm

' Processing when the "Run Process" button (ExecuteButton) is clicked
Private Sub ExecuteButton_Click()
    ' Call the ProcessData macro written in Module1
    Call Module1.ProcessData
End Sub

Explanation of the Code

Call Module1.ProcessData

This is the core command to run the macro from the form.

• Call: A keyword used to run another procedure. (It is optional, but using it makes the code clearer).
• Module1.: Specifies which module contains the macro. This ensures the correct macro is called even if different modules have macros with similar names.
• ProcessData: The name of the macro you want to run.

MainForm.Show vbModeless

This command displays the UserForm.

• vbModeless: This argument opens the form in “Modeless” mode. This allows you to select cells and edit the sheet while the form remains open.
• If you want to lock the Excel sheet until the form is closed (Modal mode), use vbModal or simply omit the argument.

How to Run

1. Run the ShowMainForm macro from the Standard Module.
2. The MainForm will appear.
3. Select any cell in your Excel sheet.
4. Click the “Run Process” button on the form.
5. The date and time will appear in the selected cell, and a message box will confirm completion.

Summary

In this article, we covered the standard method for calling a macro in a Standard Module from a UserForm.

• Use Call ModuleName.MacroName inside the button’s click event.
• Separate your interface (Form) from your logic (Module) for better code structure.
• Use .Show vbModeless if you want to interact with the sheet while the form is open.

Mastering this structure is an essential first step toward building complex and interactive VBA tools.