How to Create a Macro Button in Excel (6 Effective Ways)

When you are working with large datasets and need to repeat the same task, of course, running a macro can save you time. However, instead of opening the Developer tab each time, you can make the task much faster by creating a macro button. Through it, you can trigger your macro directly on your sheet with just one click.

On top of that, creating a macro button in Excel is not a very difficult task. In this article, we will guide you through six practical methods to add a macro button in Excel. The methods include creating a button using Form Control, ActiveX Control, Excel Shapes, adding it to the Quick Access Toolbar, placing it in your own custom group on the ribbon, and using VBA.OnAction.

What Does Creating a Macro Button in Excel Mean?

Simply, creating a macro button in Excel means you want to add a clickable item to your sheet that runs a macro whenever you press it. This button lets you run a task without going to the Developer tab each time. This also enhances the usability of your workbook. That’s especially when you want others to use your macros easily.

In the below dataset, we have students’ exam scores. Our goal is to highlight Math scores above 90 using a VBA macro. To make this task easier to run, we will create a macro button on Excel using six different methods.

Here is the HighlightHighMathScores macro that we’ve added in the VBA editor, and it will create a button in the Excel sheet to run.

Sub HighlightHighMathScores()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name if needed
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Clear previous highlights in Math column
    ws.Range("B2:B" & lastRow).Interior.ColorIndex = xlNone
    ' Highlight cells in Math column with scores > 90
    For i = 2 To lastRow
        If ws.Cells(i, 2).Value > 90 Then
            ws.Cells(i, 2).Interior.Color = RGB(255, 255, 0) ' Yellow highlight
        End If
    Next i
    MsgBox "Math scores above 90 are highlighted.", vbInformation
End Sub

It is the easiest way to create a macro button in Excel. Through this method, you can place the macro button anywhere in your sheet. They are easy to add and work well for simple Excel tasks.

Steps:

➤ Click on the Developer tab >> Insert >> choose Button under Form Controls.

➤ Click on the worksheet location where you want the Excel button to appear. The Assign Macro dialog box will appear.
➤ Assign your macro to the button >> Click OK.

➤ Right click on the button >> Edit text.
➤ Type macro name something like Highlight Top Scores or anything you prefer.

➤ Your Highlight Top Scores macro button will appear in your selected location on the Excel sheet.
➤ Click the button, and the macro will run anytime.

The ActiveX control button lets you run your macro with more control directly from your sheet. You can write or call the macro directly inside the Command Buttons.

Steps:

➤ Go to Developer >> Insert >> Command Button under ActiveX Controls.

➤ Click on the Excel worksheet location where you want the CommandButton to appear.

➤ Double-click the button. This opens the worksheet code window with a CommandButton1_Click() sub.
➤ Add your VBA code name to the click event. For example: Call HighlightHighMathScores. You can find your macro by clicking Macros in the Code group.

➤ Close the VBA editor.
➤ Click Design Mode again to turn it off.
➤ Your ActiveX command button is ready to use.

In Excel, you can use shapes as macro buttons. The method gives you more design flexibility. So, you can place the button anywhere in your sheet and style it however you want.

Steps:

➤ Click on Insert >> Shapes >> Choose a preferred shape for your button.

➤ Drag and drop your selected shape in your preferred location in the Excel sheet.
➤ Right click on the button >> select Edit Text. Type Highlight Scores or your preferred name for the macro to change the button label. You can also format your Shape button the way you want.

➤ Right click on the button >> choose Assign Macro… to specify your macro.

➤ In the Assign Macro dialog box, choose your macro name (HighlightHighMatchScores) >> click OK.

➤ It will create the ‘Highlight Scores’ button.
➤ Click the button, and it will trigger the micro operation.

Apart from adding the macro in your specific Excel sheet, you can pin the macro to the Quick Access Toolbar at the top of Excel. It will let your macro run with just one click, no matter which Excel sheet or tab you are on.

Steps:

➤ Go to File >> Options >> Quick Access Toolbar >> Macros from Choose commands from list.

➤ Select your macro name >> Click Add

➤ Choose Modify…

➤ In the appear dialog box, type the Display name and choose a Symbol you want >> Click OK.

➤ The new button will appear on your Quick Access Toolbar, where you just need to click the button to run your macro.

If you have multiple macros that you need to use frequently, you can add those to your own group in the Excel ribbon. It makes it easy for you to find your macro and run it anytime. Here, you will create a new group and add all your popular macros to that group as a button.

Steps:

➤ Go to File >> Options >> Customize Ribbon.

➤ Under Customize the Ribbon, choose the tab where you want to add your own group. We’ve selected Home.
➤ Select New Group. It will add a New Group (Custom) to the tab you picked.

➤ Click Rename… and type the Display name you want (spaces are allowed in button names) >> Click OK.

➤ To add the macro in the group, choose Macros from the Choose commands from list.

➤ Select your Macro name >> Click Add

➤ The macro will be added to the new group.
➤ Click Rename…

➤ Type the name you want in the Display name box.
➤ Select a button icon for your macro >> Click OK.

➤ Click OK again.

➤ Your new group (My Macros) will appear in the tab (Home) you picked. You can click the button (Highlight Scores) to run the macro.

You can also use the VBA code to create a macro button in your Excel. This way, Excel adds a button to your sheet, and it runs your macro when you click the button. You can use the method if you want the button to appear automatically without creating it manually.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Add a separate macro to create the button in your Excel sheet. You can just copy and paste the below VBA code.

Sub CreateMacroButton()
Dim btn As Button
Dim ws As Worksheet
Dim lastRow As Long
Dim topPos As Double
Set ws = ActiveSheet
' Get the last used row in column A (Student Name)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Calculate top position for the button (below the last row)
topPos = ws.Rows(lastRow + 2).Top ' Adds a bit of spacing below the table
' Create the button
Set btn = ws.Buttons.Add(100, topPos, 150, 30)
With btn
.Caption = "Highlight Scores"
.OnAction = "HighlightHighMathScores"
End With
End Sub

➤ Close the VBA editor.
➤ Press  Alt  +  F8  back in your Excel sheet.
➤ Select CreateMacroButton from the Macro name list>> Click Run.

➤ A new button will appear on your sheet named ‘Highlight Scores’.
➤ Click the new button on your sheet, and it will highlight the Math scores above 90.

Frequently Asked Questions

How do I trigger a macro in Excel?

➤ Press  Alt  +  F8  to open the macro window.
➤ Select your desired macro that you want to run.
➤ Click Run. 

How to create an action button in Excel?

➤ Go to the Developer >> Insert
➤ Choose either Button (Form Control) or Command Button (ActiveX Control)
➤ Draw the button on your worksheet, and assign a macro to it.

How to create a macro key in Excel?

➤ Press  Alt  +  F8  to open the Macro dialog.
➤ Select the macro you want to assign >> Click Options.
➤ In the Shortcut key box, type a letter like  Ctrl  +  Shift  +  M .
➤ Click OK.

Wrapping Up

In this quick tutorial, we have learnt how to create a macro button in Excel using six different methods. Each method gives you a faster way to run macros with just one click. Feel free to download the practice worksheet and see which method works best for your Excel tasks.