Excel Assign Macro to Button (3 Suitable Examples)

In order to run a macro we need to go to the developer tab and choose your desired macro. You also can do it from the View tab. But if it is some kind of code that you need to run multiple times then it can be a bit cumbersome and irritating. To resolve this issue we can assign this macro to a button. In this way, the process will be very easy to execute. In this article, we will show how we can assign macro to button in Excel in detailed steps.

Below we showed a simple method where we assigned a macro to a button. We created this Form Control button from the developer tab in Excel.

We showed 3 separate examples of Excel assigning a macro to a button. Here we have a dataset where we have Student information. We want to sort this data according to the age of the students. We will create a VBA macro SortData and will attach this macro to a button. User needs to understand the methods first and then try to implement the methods according to their needs. In order to avoid any kind of compatibility issue, try to use the Excel 365 edition.

1. Using Excel Command Buttons to Assign Macro

Assigning Macro to buttons can be done for two separate types of form types, one is the ActiveX control and another one is the Form Control.

1.1 Using Form Controls Feature

In this method, we need to add a Form Control in the worksheet, and then assign macro code to it.

• Go to the Developer tab > Insert tab > Form Control From there click on the Command Button icon.

• Then draw the button on the worksheet.

• After drawing the button in the worksheet, the command button will look like the below image.

• Now we need to add a code that will sort data in the worksheet.
• For this, go to the Developer tab > Visual Basic.

• Go to the Insert > Module.

• Then in the code editor window, enter the following code.

Sub SortData()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

• Save the code and close the code editor.
• Now go back to the worksheet and then right-click on the button created earlier.
• Next, from the context menu, click on the Assign Macro.

• In the Assign Macro window, select SortData.
• Click OK.

• This code is going to be attached to this Form Control button.
• Pressing this button will trigger the code, and we can see that the dataset is in the range of B5:G9, based on the Age column.

Read More: How to Use Form Controls in Excel (Detailed Analysis)

1.2 Assigning Macro to ActiveX Controls

We will add the ActiveX control button from the Developer tab and then will assign the Macro to it.

• In order to add an Activex Control button, go to the Developer tab > Insert tab > ActiveX Control From there click on the Command Button icon.

• Then draw the button in the worksheet, and then right-click on it.

• From the context menu, click on the View Code.

• And then paste the code into the code editor.

Private Sub CommandButton1_Click()
Dim sortRange As Range
Set sortRange = Range("B5:D9")
sortRange.Sort key1:=Range("C4"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

• Click on the Save command and close the editor.
• Then the code will be assigned to the button.
• After that, if we press over the button then we can see that the code executed and the data in the B5:G9 is now sorted based on the Age column.

2. Creating Shape as a Button and Assign a Macro in Excel

Apart from creating buttons and then assigning code to them, we can also create various shapes and then assign code to them.

• First, go to the Insert tab > Shapes > Rectangle. From there pick the round-edged rectangular boxes.

• Then draw on the worksheet.

• Now we need to add a code that will sort data in the worksheet.
• For this, go to the Developer tab > Visual Basic.

• After Visual Basic opens, go to the Insert > Module.

• Then in the code editor window, enter the following code.

Sub SortData()
Dim sortRange As Range
Set sortRange = Range("B5:G9")
sortRange.Sort key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlSortColumns
End Sub

• Now in the dataset, select the shape, right-click on it, and then from the context menu, click on the Assign Macro.

• From the Assign Macro window, select SortData.
• Next, click on OK.
• After that, if we click on the Shape, we can see that the code executes and the dataset in the range of B5:G9 is sorted according to the Age column.

Read More: How to Create a Macro Button in Excel (3 Quick Methods)

Similar Readings

• Excel Button to Print Specific Sheets (With Easy Steps)
• Text Alignment with VBA Command Button in Excel (5 Methods)
• How to Hide Columns with Button in Excel (4 Suitable Methods)
• Excel Macro Security (2 Necessary Aspects)
• Radio Button in Excel Without Macro (Insert, Copy, Group & Delete)

3. Using VBA Macro to Create Excel Button and Assign Another Macro

This macro will add a button to the worksheet automatically and will assign the desired code to it.

• We can enter the following code in the code editor and click on the Run option.

Sub AddButtonAndAssignMacro()
Dim btn As Button
Dim rng As Range
Set rng = Range("I4:J5")
Set btn = ActiveSheet.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
btn.OnAction = "SortData"
btn.Caption = "Sort Data"
End Sub

• We will see that there is a command button, and then we can left-click on it.
• And this button has actually already been assigned to the code SortData mentioned above.

• We can test this code by clicking on the button.
• After clicking on the button, we can see that the data in the range of cell B5:G9 is now sorted according to their Age.

Read More: VBA Code for Save Button in Excel (4 Variants)

How to Assign a Macro to a Keyboard Shortcut Button in Excel

Here, we will show how you can assign a macro to a keyboard shortcut in Excel. Pressing that destined shortcut will run the macro automatically.

• To assign a macro code to a shortcut button in Excel, you can follow the below steps.
• Go to the Developer tab > Macros.

• Then you will see the list of macros saved in the worksheet.
• And we have a macro named as SortData, this macro basically sorts data in a selected range of cells. We want to assign this macro to a keyboard shortcut.
• For this, click on Options.

• Then in the Shortcut key: press Shift+R.
• This will basically mean that your macro is now assigned to the Ctrl+Shift+R shortcut key.
• Click OK after this.

• After pressing OK we can see that the data stored in the range of cell B5:G9 is now sorted according to their Age.

Read More: Excel Macro Shortcut Key (2 Suitable Ways)

How to Insert Macro Buttons into Excel Ribbon

Apart from adding a button in the worksheet and then assigning a macro to it, we can add a Macro button to the ribbon menu and run code from there.

1. Add Macro Button to Quick Access Toolbar

• Right-click on the ribbon menu and select “Customize Quick Access Toolbar” or click on the small drop-down arrow to the right of the Quick Access Toolbar and select “More Commands.“

• It will open the Excel Options Then go to the Quick Access Toolbar menu, select Macros from Popular Commands and click on the Add.

• After clicking on the Add command, we can see that the Macros are now in the right panel.
• Click OK after this.

• Finally, we can see that the Macro button is now attached to the ribbon.

Read More: How to Add Option Button in Excel (With Easy Steps)

2. Add a Macro Button to Your Own Group on the Ribbon or Toolbar

You can add a dedicated macro button to the Excel ribbon custom tab and then run the macro directly from there.

• For this, first, you need to right-click on the ribbon menu and from there, click on Customize the Ribbon.
• Then from the Excel Options tab, go to Quick Access Toolbar > click on the New Tab in the Customize the Ribbon. Rename the Tab and the Group underneath it.
• Then from the Choose commands from the drop-down menu, select Macros.
• Right after selecting the Macros, there will be a list of macros stored in the worksheet.
• Select SortData, and then click on Add.
• We can see that the SortData has now been added to the Group.
• Click OK after this.
• After this, you will notice that there is a new tab in the ribbon and upon switching to it we can see that there is the SortData macro button within another Group.
• Pressing the SortData button will trigger the macro and we will see that the dataset in the range of B5:G9 is now sorted based on the Age column.

Read More: How to Group Radio Buttons in Excel (2 Simple Methods)

Things to Remember

• Choose a descriptive name: Give your macro a descriptive name that explains what it does, so it’s easy to understand and identify later.
• Test the macro: Before assigning the macro to a button, make sure it works correctly and does what you want it to do.
• Consider security: Be cautious when running macros, as they can potentially contain harmful code. Make sure that you trust the source of the macro and have enabled macros in your Office program.
• Choose an appropriate button location: Choose a button location that makes sense and is easy to access. The Quick Access Toolbar, Ribbon, or your own custom group on the Ribbon or Toolbar are good options.
• Use a clear icon or image: If you choose to use an icon or image for your macro button, make sure it’s clear and easy to understand.
• Organize your macros: If you have multiple macros, consider organizing them into groups or categories to make them easier to find and access.
• Document your macros: If you’re sharing your macros with others, be sure to document them thoroughly, including what they do and any specific instructions for running them.

Frequently Asked Questions

1. How do I record a macro with a button?

We can record Macro easily by clicking over the Record Macro from the Developer tab.

After that whatever action you take, will be recorded and can be repeated in the same process if you want.

2. How do I run a macro in Excel when clicking a specific cell?

Yes, you can do that by using the below code. For example, let’s say you have a macro stored in a MyMacro. Now if you want to run this code while clicking on a specific cell, then enter this code in the worksheet and save it.

After that, whenever you click on a cell, it will run the MyMacro. Use or modify the code according to your own need.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call MyMacro
End If
End Sub

3. How do I edit a Macro Button?

We can edit the macro button by right-clicking the button, and then we can see that there is another tab as Shape Format that is now present in the ribbon.

From this tab, we can see that there are numerous options to modify the appearance of the button.

Download Practice Workbook

You can download the Excel workbook from here.

Conclusion

Here, in this article, we have shown Excel assign macro to button, whether they are ActiveX Control or Form Control. We also have shown the VBA Macros method. We showed how you can assign the macro to the keyboard shortcut button. Finally, we also demonstrated how you can insert a macro button to the ribbon menu and the customized ribbon menu.  For any further queries, comment below.

Related Articles

• How to Make a Calculate Button in Excel (with Easy Steps)
• How to Set Option Button Value in Excel VBA (4 Easy Steps)
• Print to PDF Using Macro Button in Excel (5 Macro Variants)
• Excel VBA: Form Control Checkbox Value (3 Examples)
• How to Remove Macros from Excel (5 Suitable Ways)
• How to Delete Row If Cell Contains Value Using Macro in Excel