WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Create Button Macro in Excel

Create Button Macro in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Create a Macro Button in Excel

The most common tasks in Excel can be automated to avoid unnecessary time consumption. Having talked about automation VBA macros is the way to go forward in excel; once the macro coding is done, 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 by just click of the button, the assigned job will be executed. So in this article, we will take you through the process of creating an excel button to execute the task.

3 Methods to Create Button in Macro Excel?

As we know, macro coding is the perfect way to automate tasks; there is a small other thing we can do to execute that code. Macro & Buttons are the perfect combinations; it is like a “typical Indian marriage of boy likes a girl and girl likes a boy.” By just as the click of the button, we can execute hundreds of lines, so some of the complicated reports can be automated and hand over to your team members and say all they have to do is to click the button to get the job done.

So, in excel, we can create an excel button using three ways, and those are below.

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

Excel Create Button

We will see each one of them in detail now.

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

#1 – Using Shapes

The best and easy way to create a button to assign macro is using “Shapes” of 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.

Create Button Example 1

We have data in columns from A to E, and in 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 and 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 need to open the Visual Basic Editor, and we need to run the code.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Step 1: Go to the INSERT tab in excel and find the “Shapes” option.

Create Button Example 1-1

Step 2: Once you click on the drop-down list of “Shapes,” we will see many shapes options.

Create Button Example 1-2

Select any of the shapes and draw on the worksheet (above the data table).

Create Button Example 1-3

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

Create Button Example 1-4

Step 4: Enter text as you would like to convey the message to the user.

Create Button Example 1-5

Step 5: This is the default view of the shape, so we can format it under the “Format” tab.

Example 1-6

Step 6: Once the proper formatting is given to the shape right click and choose “Assign Macro.”

Example 1-7

Step 7: Now, this will open up all the macro names in the visual basic editor.

Example 1-8

Choose the macro that you would like to assign to the shape.

Example 1-9

Step 8: Now click on “Ok,” and macro has been assigned to the shape.

Now when you hover on the shape, you can see the hand pointer indicating the click option.

Example 1-10

Now, if you click, the assigned macro will be executed, and the value which is there in cell G2 will be filtered.

Example 1-11

#2 – Using Form Control Shapes & Active X

Under the developer tab, we have an option called “Insert”; under this; we have “Form Control” and “Active X Control.”

Use Form Control 1

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

Use Form Control 1-1

Draw this on your worksheet, and immediately you will get a list of macros.

Use Form Control 1-2

Choose the macro that you would like to assign and click on “Ok” to close the above window.

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

Things to Remember

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

Recommended Articles

This has been a Guide to Create Button Macro in Excel. Here we discuss the Top 3 Methods using shapes, active X control, and form control along with examples and downloadable excel templates. You may learn more about excel from the following articles –

  • VBA Macros
  • Record Macros in Excel
  • VBA Tutorial
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Create Button Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More