Create Button Macro In Excel

We Create A Macro Button In Excel to assign the Macro code and make it interactive, easy to access, and save time. Once the coding is complete, we execute the program to get the job done.

An interactive button can perform many activities in the worksheets. Therefore, 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.

We can create a button in three methods in Excel, namely:

1. Using Shapes.
2. Using Active X.
3. Form Control in Excel.

We will consider specific examples for the methods to Create Button Macro in Excel.

Example #1 - Using Shapes

Using the “Shapes” feature in Excel, we can insert a wide variety of shapes, and also format them.

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, and the card type that we choose in the G2 cell should be filtered out in the data. For this, below is the excel macro 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 must open the Visual Basic Editor, and run the code.

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

Follow the below-given steps to assign macro to shape.

1. We must first go to the “Insert” tab in Excel and find the “Shapes” option.

   
   

2. Once we click on the “Shapes” drop-down list, we see many shape options. Select any of the shapes and draw on the worksheet.

   
   

3. After adding the shapes, we need to enter some text values, so right-click on the shape and choose “Edit Text.”

   
   

4. We must enter the text as we would like to convey the message to the user.

   
   
   
   It is the default view of the shape.

5. We can format the shape under the “Format” tab.

   
   

6. Once the proper formatting is given to the shape, we need to click and choose “Assign Macro.”

   
   

7. Once the proper formatting is given to the shape, right-click and choose “Assign Macro.” Next, we must select the macro we want to assign to the shape.

   
   

8. Now click “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.
   
   

Example #2 - Using Form Control Shapes & Active X

We will consider a combined example for the other 2 methods, Form Control Shapes & ActiveX.

Under the developer tab, we have an option called “Insert”. Under this, we have “Form Control” and “ActiveX Control.”

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

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

We must choose the macro we want to assign and click “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.

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