Create a Macro Button in Excel

We can automate the most common tasks in Excel to avoid unnecessary time consumption. Regarding automation, VBA macros are the way to go forward in Excel. Once the macro coding is completed, we need to execute that program to get the assigned job done. You must have already seen a button that can do the job in many of the worksheets, so once the programming is done, we can assign that work to one of the buttons so that the given job will be executed by click of the button. So in this article, we will take you through creating an Excel button to perform the task.

3 Methods to Create Button in Macro Excel?

As we know, macro coding is the perfect way to automate tasks. However, there is another small thing we can do to execute that code. Macro and buttons are the ideal combinations. It is like a “typical Indian marriage of boy likes a girl and girl likes a boy.” With just the click of the button, we can execute hundreds of lines, so some of the complex reports can be automated and handed over to our team members. But then, we have to click the button to get the job done.

So, we can create a button in three ways in Excel. Those are below:

  • ShapesActive X ControlForm Control in ExcelForm Control In ExcelExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.read more

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Create Button Macro in Excel (wallstreetmojo.com)

We will see each one of them in detail now.

#1 – Using Shapes

The best and easiest way to create a button to assign a macro is using “Shapes” in Excel. We can insert a wide variety of shapes in Excel, and also we can format these shapes as we would like to make them look.

Before inserting shapes, below is the macro we had written to filter values in the below data.

We have data in columns from A to E. In the G2 cell, we have created a drop-down list of all the card types, the card type that we choose in the G2 cell should be filtered out in the data. For this, below is the excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more code we have written.

Code:

Sub Filter_Values()

Dim LR As Long Dim LC As Long Dim Rng As Range

LR = Cells(Rows.Count, 1).End(xlUp).Row LC = Cells(5, Columns.Count).End(xlToLeft).Column

Set Rng = Cells(5, 1).Resize(LR, LC) Rng.AutoFilter Field:=4, Criteria1:=Range(“G2”).Value

End Sub

Upon running the above code, it will filter the values which are selected in the cell G2 cell. Every time we need to open the Visual Basic EditorVisual Basic EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more, and we need to run the code.

So, now we will insert shapes to assign the macro.

#2 – Using Form Control Shapes & Active X

Under the the developer tabThe Developer TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more, we have an option called “Insert.” Under this, we have “Form Control” and “Active X Control.”

  • We must first go to the “Insert” tab in Excel and find the “Shapes ” option. Once we click on the ” Shapes ” drop-down list, we see many shape options. Select any of the shapes and draw on the worksheet. After adding the shapes, we need to enter some text values, so right-click on the shape and choose “Edit Text.” We must enter the text as we would like to convey the message to the user. It is the default view of the shape. We can format the shape under the “Format” tab. Once the proper formatting is given to the shape, we need to click and choose “Assign Macro.” Once the proper formatting is given to the shape, right-click and choose “Assign Macro.” Next, we must select the macro we would like to assign to the shape. Now click on “OK,” Currently, the macro has been assigned to the shape. We see the hand pointer indicating the click option when we hover on the shape. If we click, it will execute the assigned macro, and the value in cell G2 will be filtered.

It is the default view of the shape.

We see the hand pointer indicating the click option when we hover on the shape. If we click, it will execute the assigned macro, and the value in cell G2 will be filtered.

From “Form Controls,”  we must select the “Button” option.

Then, draw this on the worksheet, and immediately we will get a list of macros.

We need to choose the macro we would like to assign and click on “OK” to close the above window.

As a result, now, a button is created to run the macro.

Like this, we can create a button in Excel to execute the macro.

Things to Remember

  • The Active X Control button requires further coding knowledge, so not covered in this article.The basic difference between “Shape” and “Form Controls” is the formatting and style they look like.

This article has been a guide to Create Button Macro in Excel. Here, we discuss the top 3 methods using shapes, active X control, form controls, examples, and downloadable Excel templates. You may learn more about Excel from the following articles: –

  • VBA MacrosRecord Macros in ExcelVBA Tutorial