How to Add Pop-Up on a Button to Select a Macro in Excel VBA

Adding a button that triggers the Assign Macro pop-up is one of the simplest ways to make your Excel workbooks interactive. Instead of remembering shortcut keys or running macros from the Developer tab, you can attach them to buttons or shapes that users can click. Every time you insert a Form Control button or assign a macro to a shape, Excel automatically launches the Assign Macro pop-up, letting you pick the macro you want to link.

In this article, you’ll learn three practical methods to add a pop-up on a button to select a macro. We’ll cover using Form Control buttons, assigning macros to shapes, and reopening the pop-up on existing buttons or shapes to make changes later.

Key Takeaways

Steps to Insert a Form Control Button to Open Assign Macro Popup:

➤ This is the dataset we will be using to demonstrate the methods
➤ Go to the Developer tab >> Insert >> Form Controls >> Button
➤ Draw the button anywhere on your worksheet
➤ The Assign Macro popup will appear automatically
➤ Press Alt + F11 , choose Insert → Module, and paste this code:

Sub HighlightTopPerformers()
    Dim ws As Worksheet
    Set ws = Sheets("MacroDemo")
    ws.Range("A2:D9").Interior.ColorIndex = 0 ' Clear previous colors
    Dim cell As Range
    For Each cell In ws.Range("D2:D9")
        If cell.Value >= 90 Then
            cell.EntireRow.Interior.Color = vbYellow
        End If
    Next cell
End Sub

➤ From the pop-up list, select HighlightTopPerformers and click OK
➤ The button is now assigned; clicking it will instantly highlight employees with scores ≥ 90

Download Practice Workbook

Insert a Form Control Button to Open Assign Macro Popup

One of the quickest ways to add a button that brings up the Assign Macro popup is by using a Form Control button. When you draw the button on your worksheet, Excel automatically prompts you to assign a macro to it. This makes it perfect for connecting actions like highlighting top performers in your dataset.

This is the dataset that we will be using to demonstrate the methods.

Using our dataset, if you insert a Form Control button and select the HighlightTopPerformers macro from the popup, clicking the button will highlight the rows of employees with scores greater than or equal to 90 (Lisa Wong and Emma Brown).

Steps:

➤ Go to the Developer tab >> Insert >> Form Controls >> Button

➤ Draw the button anywhere on your worksheet

➤ Press Alt + F11 , choose Insert >> Module

➤ Paste this code:

Sub HighlightTopPerformers()
    Dim ws As Worksheet
    Set ws = Sheets("MacroDemo")
    ws.Range("A2:D9").Interior.ColorIndex = 0 ' Clear previous colors
    Dim cell As Range
    For Each cell In ws.Range("D2:D9")
        If cell.Value >= 90 Then
            cell.EntireRow.Interior.Color = vbYellow
        End If
    Next cell
End Sub

Explanation

➧ A Form Control button is directly linked with the Assign Macro popup when inserted
➧ The popup lets you choose any available macro in your workbook
➧ Assigning HighlightTopPerformers will highlight the full rows of employees with scores ≥ 90

➤ The Assign Macro pop up will appear automatically.
➤ From the list, choose HighlightTopPerformers and click OK.

➤ Once the macro is assigned, clicking the button executes it instantly

Use a Shape as a Button to Open Assign Macro Popup

If you want more flexibility in how your buttons look, you can use shapes instead of the default gray Form Control buttons. Any shape you insert in Excel can be turned into a button by assigning a macro to it. When you right-click the shape and choose Assign Macro, Excel will bring up the Assign Macro popup, allowing you to connect it with your desired macro.

In our dataset, if you insert a rectangle shape and assign the macro SortByScore, clicking the shape will rearrange the table so that employees are listed in order of their scores, with the highest scores appearing at the top.

Steps:

➤ Go to the Insert tab >> Shapes and pick a rectangle (or any other shape)

➤ Draw the shape on the worksheet near your dataset

➤ Press Alt + F11 , choose Insert >> Module and past this code

Sub SortByScore()
    Sheets("MacroDemo").Range("A1:D9").Sort _
        Key1:=Sheets("MacroDemo").Range("D1"), Order1:=xlDescending, Header:=xlYes
End Sub

Explanation

➧ Any inserted shape can be used as a button by assigning a macro
➧ Right-clicking and choosing Assign Macro is what triggers the popup
➧ Assigning the SortByScore macro will reorder the employee dataset by their scores, highest to lowest

➤ Right-click the shape and select Assign Macro
➤ The Assign Macro popup will open
➤ From the list, select SortByScore and click OK

➤ Once assigned, clicking the shape runs the macro immediately

Frequently Asked Questions

How do I make Excel show the Assign Macro pop-up automatically?

Insert a Form Control button from the Developer tab. As soon as you draw it on the sheet, Excel will automatically launch the Assign Macro pop-up so you can assign a macro right away.

Can I reopen the Assign Macro popup later if I want to change the macro?

Yes. Simply right-click the button or shape you created and choose Assign Macro. This reopens the pop-up, allowing you to update or switch the assigned macro. The key difference is that a Form Control button automatically opens the Assign Macro popup when inserted, while a Shape requires right-clicking >> Assign Macro, but Shapes allow greater design flexibility with custom colors, styles, and text.

Does an ActiveX button show the Assign Macro pop-up?

No. An ActiveX Command Button does not trigger the pop-up. Instead, you add code directly in the VBA editor under its Click event.

Wrapping Up

Adding a pop-up on a button to select a macro in Excel makes your workbooks more user-friendly and interactive. Whether you’re inserting a Form Control button (which opens the popup automatically), using a Shape as a button (triggering the popup manually), or reopening the popup to change macros later, each method gives you flexibility in how macros are assigned and executed.

Post Views: 7