Excel Assign Macro to Button (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Assign Macro to Button


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.

Add form control command button from developer tab

  • Then draw the button on the worksheet.

Drawing the button on the worksheet

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

Button on the worksheet after drawing it to assign a macro to the button in Excel

  • Now we need to sort data in the worksheet with Excel VBA.
  • For this, go to the Developer tab > Visual Basic.

VBA editor Initialization

  • Go to the Insert > Module.

Inserting 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

 VBA code for sorting values

  • 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.

Assign macro to the button from context menu in Excel

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

select which macro to assign

  • 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.

Sorted values after pressing Button


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.

Add activex control form from Developer

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

Drawing Button on the Worksheet

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

open the code editor of the button to assign macro to the button in Excel

  • 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

The code in the code editor

  • 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.

Sorted values after pressing the Activex button


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.

Add shapes in the worksheet

  • Then draw on the worksheet.

Draw the shapes on the worksheet to assign macro to the shape button in Excel

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

VBA editor Initialization

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

Inserting 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

 VBA code for sorting values

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

Assign macro to shape button in Excel

  • 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.

Sorted dataset after pressing the shape


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

This macro will create a macro button for 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

Running VBA macro to add a button to the worksheet and assign a VBA macro to that button in Excel

  • 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.

Button present in the worksheet after pressing the RUN command with assigned code

  • 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.

Sorted data after pressing the button


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.

Macros option open from the developer tab

  • Then you will see the list of saved macros 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.

Macros option open from the developer tab

  • 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.

Assigning the required keyboard button

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

Assign the required keyboard button in Excel


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.

Assign the Macro button command to the quick-access toolbar in Excel

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

Macro addition from choose commands from to right panel

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

Addition of Macro from choose command from

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

Assign Macro button in the quick access menu in Excel


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.

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.

Record macro from developer tab

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 needs.

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

Code to run macro while selecting a specific cell

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.

Shape tab to modify 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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo