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

Get FREE Advanced Excel Exercises with Solutions!

When we frequently use the macro in Excel for particular applications then it’s quite bothering or time-consuming to run the macro by opening the Macros dialog box repeatedly. To overcome these situations Excel offers some amazing ways to make our customized macro button. So, today we are going to show you 3 fast ways to create a macro button in Excel.


Watch Video – Create a Macro Button in Excel


How to Create a Macro Button in Excel: 3 Quick Methods

Here’s the dataset that we’ll use to explore the methods, it contains some salespersons’ sales.

How to Create a Macro Button in Excel

And, we’ll use this code to assign to macro buttons which we placed in a module, it will return the used row numbers in our active sheet.

How to Create a Macro Button in Excel


1. Using Control Button to Create a Macro Button

Firstly, we’ll apply the Control buttons from the Developer ribbon to build a macro button. You will get two types of buttons here, the Form Controls button and the ActiveX Controls button. Both perform the same kind of operations but ActiveX Controls provides more customizations, formats, and features.


1.1. Using Form Control Button

From the Form Controls section, we’ll apply the first control button named- Button.

Steps:

  • So, click as follows: Developer > Insert > Form Controls > Button.

Choosing Form Control Button to Create a Macro Button

Then your cursor will look like this- a plus sign.

Set Size to Create a Macro Button

  • Left-click your mouse and hold, then drag over the sheet where you want to place the button.

Rectangle Macro Button

  • After releasing the left click, the Assign Macro dialog box will appear where you can assign macro to the button.
  • Select the Macro name and press OK.

Select the Macro name to Create a Macro Button

Macro is assigned successfully, you can change the button name too. By dragging the eight circular icons beside the box, we can edit the size of the macro button.

  • Double-click on the button and type the name. We named it- Used Rows.

  • If you right-click on the button you will get many options to edit the macro button like Cut, Copy, Paste.

  • So now, if we just click on the button it will run the macro and show the output.

See, a notification box is showing the output.


1.2. Using ActiveX Control Button

Now we’ll use the Command Button from the ActiveX Controls buttons. But instead of assigning a macro, we’ll have to type the code here.

Steps:

  • Click as follows: Developer > Insert > ActiveX Controls > Button.

Using Control Button to Create a Macro Button

  • Like the previous section, set the button size using the mouse.

Set Size to Create a Macro Button

  • Next, right-click on the button and select View Code from the context menu.

By using the other menu you can edit the macro button as you require.

Insert Code to Create a Macro Button

  • After that, a VBA window will open up like the image below that will have the Sub and End procedures by default. We’ll have to place codes within it.

  • Copy the codes from the module and place them here.
  • Later, just press the Run icon to activate the codes for the button.

Now just by clicking the button, we’ll be able to run the codes.


2. Inserting Shapes to Create a Macro Button

We know, Excel has a Shapes feature in the Insert ribbon. The amazing part is, we can assign macro with any kind of shape too.

Steps:

  • Go to Insert ribbon, then choose your preferred shape from the Shapes We chose Rectangle with Rounded Corners.

Inserting Shapes to Create a Macro Button

  • Again by following the first section, set the button size.
  • From the Shape Format ribbon, you can change the shape style too. We chose- Blue, Accent 5 style.

Choose Shape Style for the Macro Button

  • Next, double-click on the button and type the name.

Set Name for the Macro Button

  • After that, right-click on the button and select Assign Macro from the context menu.

A few moments later, the Assign Macro dialog box will appear.

  • Select the macro and press OK.

  • Nothing to do more, just click the button to run the macro.


3. Using Quick Access Toolbar to Make a Macro Button

The previous method was to set a button in a sheet but what if we could make a button that we could use anytime for any sheet? Maybe it will be very useful for some particular situations. Here we’ll add this type of button to the Quick Access Toolbar.

Steps:

Using Quick Access Toolbar to Make a Macro Button

  • At this moment, choose Macros from the Choose commands from drop-down box.
  • Soon after, the available macros will appear here. Select our inserted macro name and press Add>>.

Set Macro Name for the Button

  • By clicking on the Modify button we can change the button symbol.

Modify the Macro Button Icon

  • We used the Play icon for the button.

  • Finally, nothing to do more, just press OK.

Have a look, the button is available in the toolbar. Now it doesn’t matter which sheet you are in, you will get it available for any sheet.

  • Click on the button and it will work for the active sheet.

The output after clicking on the button.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. We hope the procedures described above will be good enough to create a macro button in Excel. Feel free to ask any question in the comment section and please give us feedback.

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.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo