[Excel VBA] How to Dynamically Switch Macros Assigned to Buttons or Shapes (OnAction)

Introduction

Have you ever wanted to dynamically change the macro executed when clicking a button or shape on an Excel sheet based on the situation? For example, a toggle function where the same button acts as a “Start” button, but changes to a “Stop” button once clicked, switching the underlying process.

Using VBA, you can freely change the macro assigned to a shape by rewriting the .OnAction property of the Shape object.

In this article, I will explain how to create a practical “Toggle Button” that switches both its display text and the executed macro every time it is clicked.

VBA Sample Code: Switching Registered Macros

This macro targets a shape (button) named “ToggleProcess”. It switches the assignment between the StartProcess macro and the StopProcess macro each time it is clicked.

' Macro for "Start Processing"
Sub StartProcess()
    ' Write the actual processing code here
    MsgBox "Process Started.", vbInformation

    ' --- Switch button state to "Stop" mode ---
    With ActiveSheet.Shapes("ToggleProcess")
        ' Set the macro name to be executed next in the .OnAction property
        .OnAction = "StopProcess"
        
        ' Change the display text using the .TextFrame.Characters.Text property
        .TextFrame.Characters.Text = "Stop Process"
    End With
End Sub

' Macro for "Stop Processing"
Sub StopProcess()
    ' Write the actual processing code here
    MsgBox "Process Stopped.", vbInformation
    
    ' --- Switch button state back to "Start" mode ---
    With ActiveSheet.Shapes("ToggleProcess")
        ' Return the .OnAction property to the StartProcess macro
        .OnAction = "StartProcess"
        
        ' Return the display text to Start mode
        .TextFrame.Characters.Text = "Start Process"
    End With
End Sub

Preparation Before Execution

1. Place a Shape (e.g., Rounded Rectangle) or a Form Control Button on the sheet.
2. Select the shape (button) and change its name to ToggleProcess in the Name Box at the top left.
3. Right-click ToggleProcess, select “Assign Macro,” and assign the StartProcess macro initially.

Now, every time you click the button, the display text and the executed macro will switch.

Explanation of the Code

With ActiveSheet.Shapes("ToggleProcess")

This syntax accesses a specific shape object on the sheet by its name. Form Control buttons are also treated as Shape objects in VBA.

.OnAction = "StopProcess"

This single line is the core part of switching the registered macro.

• .OnAction: A property that sets or retrieves the name of the macro (as a string) to be executed when the shape is clicked.

Inside the StartProcess macro, it rewrites its own .OnAction property so that StopProcess runs on the next click. Similarly, inside the StopProcess macro, it sets it back to StartProcess.

.TextFrame.Characters.Text = "Stop Process"

This property changes the text displayed on the surface of the shape.

• .TextFrame: Accesses the text area of the shape.
• .Characters.Text: Retrieves or sets the string in that area.

By changing the display text along with the .OnAction property, users can intuitively understand what will happen the next time they press the button.

Summary

In this article, I explained how to dynamically switch macros assigned to buttons or shapes using the .OnAction property.

• Macros assigned to shapes are managed by the .OnAction property.
• By rewriting the target shape’s .OnAction property to a different macro name (string) within the code, you can change the subsequent behavior.
• Changing .TextFrame.Characters.Text at the same time creates a user-friendly toggle button.

Using this technique, you can build multifunctional interfaces in limited space and create sophisticated sheet applications that guide user operations according to the processing state.