Create Button Macro In Excel

Article byJeevan A Y
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Create A Macro Button 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.

Key Takeaways

  • The Create Button Macro in Excel help us save time while using Macros or to execute them.
  • We write a Macro, save it, and every time to execute the code, we must open it from the VBA Editor and press the Run command. When we create a button and assign an existing macro to it, with just the click of that button, we can Run the Macro code avoiding the steps we just saw.
  • We can create a button using any shapes available in the “Insert” tab, or the Form Controls or the ActiveX Control buttons from the “Developer” tab.

3 Methods To Create Button In Macro Excel

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

  1. Using Shapes.
  2. Using Active X.
  3. Form 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.
Excel-Create-Button

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

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.

Create Button Example 1

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


    Create Button Example 1-1

  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.


    Create Button Example 1-3

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


    Create Button Example 1-4

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


    Create Button Example 1-5

    It is the default view of the shape.

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


    Create Button Example 1-6

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


    Create Button Example 1-7

  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.


    Create Button Example 1-9

  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.

    Create Button Example 1-11

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 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 “ActiveX Control.”

Use Form Control 1

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

Use Form Control 1-1

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

Use Form Control 1-2

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.

Use Form Control 1-3

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

Important Things To Note

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

Frequently Asked Questions (FAQs)

1. How to enable the Developer tab?

When we want to assign a button to the Macro Code, first, we must create Macros using the option available in the “Developer” Tab. Sometimes, the “Developer” Tab will not be available on the ribbon, if we haven’t used it before. Therefore, we must first enable it.

The steps to enable the “Developer” tab are as follows:

• Step 1: Go to the “File” tab.

Create Button Macro in Excel - FAQ 1 - Step 1

• Step 2: Click the “More…” option from the list → select the “Options”.

FAQ 1 - Step 2

• Step 3: The “Excel Options” window pops up, and then click the “Customize Ribbon” on the left side of the “Excel Options” window.

FAQ 1 - Step 3

• Step 4: Check the “Developer” tab checkbox under the “Main Tabs” on the right side of the “Excel Options” window.

FAQ 1 - Step 4

• Step 5: Click “OK”. Now, the “Developer” tab is enabled and appears on the ribbon, as shown below.

FAQ 1 - Step 5

2. How to Add the Macro button to the Excel ribbon?

The steps to add the Macro button to the Excel Ribbon are as follows:

• Step 1: Go to the “File” tab.

Create Button Macro in Excel - FAQ 2 - Step 1

• Step 2: Click the “More…” option from the list → select the “Options”.

FAQ 2 - Step 2

• Step 3: The “Excel Options” window pops up.
First, click the “Quick Access Toolbar” on the left side of the “Excel Options” window.
Next, select the “Macros” option from the “Choose commands from:” drop-down.

FAQ 2 - Step 3

Immediately, the list of created and saved Macro files appears in the section below the “Choose commands from:” drop-down. We can select the required Macro Code, and click “OK”.

Finally, we can see the Macro button added to the Excel Ribbon.

3. The Create Button Macro in Excel is not working or is greyed out.

The Create Button Macro in Excel may not work for the following reasons, namely,
The Macro Code file assigned is deleted and moved to another location.
The Button is assigned to a different file or not assigned at all.

Download Template

This article must help understand Create Button Macro in Excel with its formulas and examples. You can download the template here to use it instantly.

You can download this Create Button Excel Template here – Create Button Excel Template

Recommended Articles

This article has been a guide to Create Button Macro in Excel. Here we assign macro to buttons from shapes, ActiveX & Form Controls, examples & downloadable template. You may learn more about Excel from the following articles: –