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

Introduction

When creating an operation panel using a UserForm in VBA, you will inevitably encounter situations where you want to “execute a main process written in a standard module when a button on the form is clicked.”

For example, you might want to execute a routine task, such as removing unnecessary whitespace from a selected range, with a single button click.

By keeping the UserForm code and the main processing code separate, the overall program structure becomes clearer, making future modifications and feature additions easier.

This article explains the basic method for calling a macro described in a standard module when a button placed on a UserForm is clicked, following specific steps.

Overview and Creation Steps

The overall structure of what we will create is as follows:

1. Standard Module: Write the macro that performs the main processing (removing whitespace from the selection).
2. UserForm: Create a form that serves as the tool’s operation screen and place a “Remove Spaces” button on it.
3. Form Code: Write the code to call the standard module’s macro when the “Remove Spaces” button is clicked.
4. Display Form: Write a macro in another standard module to display the created UserForm.

VBA Sample Code

Step 1: Create the Main Processing Macro (Standard Module)

First, write the main macro you want to execute in a standard module (e.g., Module1). Here, we will use an example process that applies the Trim function to remove leading and trailing spaces from each cell in the selected range.

[Standard Module: Module1]

' Main process to remove leading/trailing spaces from selected cells
Sub TrimSelectionSpaces()
    Dim targetCell As Range
    
    ' Exit if selection is not a range
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    For Each targetCell In Selection.Cells
        ' Execute only if cell value is a string
        If VarType(targetCell.Value) = vbString Then
            targetCell.Value = Trim(targetCell.Value)
        End If
    Next targetCell
End Sub

' Macro to show the UserForm
Sub ShowTextToolForm()
    ' Show TextToolForm modelessly
    TextToolForm.Show vbModeless
End Sub

Step 2: Create the UserForm

1. In the VBE, select Insert > UserForm to create a new form.
2. In the Properties window, change the form’s (Name) to TextToolForm.
3. Next, place a CommandButton from the Toolbox onto the form and set its properties as follows:
   • (Name): TrimButton
   • Caption: Remove Spaces

Step 3: Write the Button Click Event (UserForm)

Double-click the created “Remove Spaces” button to display the form’s code window. Write the code to call TrimSelectionSpaces from Module1 when the button is clicked.

[UserForm: TextToolForm]

' Process when "Remove Spaces" button (TrimButton) is clicked
Private Sub TrimButton_Click()
    ' Call the TrimSelectionSpaces macro from the standard module
    Call TrimSelectionSpaces
    
    ' Completion message
    MsgBox "Removed spaces from the selected range.", vbInformation
End Sub

Explanation and Execution Method

Call TrimSelectionSpaces

This single line is the core command for calling a macro in a standard module from a form.

• Call: A keyword used to call another procedure (macro). It is optional, but using it makes the code easier to understand.
• TrimSelectionSpaces: The name of the macro you want to call, described in Module1. If calling from a different module, writing Module1.TrimSelectionSpaces makes it more explicit.

TextToolForm.Show vbModeless

This is the command to display the created UserForm (TextToolForm).

• vbModeless: Specifying this argument opens the form in a “modeless” state, allowing you to manipulate Excel sheets while the form remains open.

How to Execute

1. Run the ShowTextToolForm macro written in the standard module.
2. TextToolForm will appear.
3. Select the cell range on the Excel sheet from which you want to remove spaces.
4. Click the “Remove Spaces” button on the form.
5. If the leading/trailing spaces are removed from the selected cells and the completion message appears, the process is successful.

Summary

In this article, I explained the basic and very important method of calling a macro in a standard module from a VBA UserForm.

• Write Call MacroName inside the button’s click event.
• Separating the UserForm (UI) and the processing (Logic) leads to a structured and easy-to-understand program.

Mastering this basic structure is the first step toward creating more complex and interactive VBA tools. Please make use of this in your own tool development.