[VBA] How to Execute a Macro by Clicking a Command Button on a UserForm

Introduction

UserForms are essential for providing graphical interfaces in VBA tools. The central element of these forms is the Command Button, which users click to trigger actions.

You can easily link specific VBA macros to a simple click action, such as “Input today’s date into the sheet when this button is pressed.”

This article explains the most basic and important steps to place a Command Button on a UserForm and execute specific code when it is clicked.

VBA Sample Code: Running a Macro via Button

In this example, we will create a process where clicking a button named RunProcessButton on a UserForm stamps the text “Processed” into the currently selected cell.

UserForm Code

Write this code directly into the UserForm’s code module.

Steps:

1. Place a CommandButton on the UserForm.
2. In the Properties window, change the (Name) to RunProcessButton and the Caption to Run Process.
3. Double-click the placed button. The VBE will automatically generate Private Sub RunProcessButton_Click(). Write your code inside this block.

' Event executed when the "Run Process" button (RunProcessButton) is clicked
Private Sub RunProcessButton_Click()

    ' Check if the current selection is a cell range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a cell before running.", vbExclamation
        Exit Sub
    End If
    
    ' Change the value and formatting of the selected cell(s)
    With Selection
        .Value = "Processed"
        .Font.Bold = True
        .Interior.Color = vbYellow
    End With

End Sub

Setting the Button Caption via Code

You can set the button’s Caption (the text displayed on the button) dynamically using VBA code instead of the Properties window. For example, you can set it in the UserForm_Initialize event, which runs just before the form appears.

' Event executed when the form is initialized
Private Sub UserForm_Initialize()
    ' Set the Caption property of RunProcessButton
    Me.RunProcessButton.Caption = "Click Here to Run"
End Sub

Explanation of the Code

Private Sub RunProcessButton_Click()

This is a special event procedure. When the control named RunProcessButton is clicked, the code written between Sub and End Sub is automatically executed. This _Click() event is the foundation of button operations.

Selection Object

Selection refers to the range of cells currently selected by the user on the Excel sheet. By using Selection.Value = "...", you can rewrite the value of the selected cells.

Me.RunProcessButton.Caption

• Me: Inside a UserForm’s code module, Me refers to the form itself. It is standard practice to use Me to access controls on the form.
• .Caption: This property controls the text displayed on the face of the button.

Summary

In this article, we covered the basic method of linking a macro to a UserForm Command Button.

• Write the processing logic inside the Private Sub ButtonName_Click() event procedure.
• You can change the text on the button using the .Caption property.

The flow of “clicking a button to execute the code in the corresponding _Click() event” is the basis of all UserForm operations. Mastering this mechanism will allow you to create interactive tools with various functions.