Pinterest Pixel

3 Easy Methods to Create Button in Excel for Macros

Do you repeat various Excel actions like copy & pasting data, formatting, putting borders, etc? Do you... read more

Download Excel Workbook
Bryan
Posted on

Steps To Follow

Overview

Do you repeat various Excel actions like copy & pasting data, formatting, putting borders, etc?

Do you know that you create button in Excel and automate your tasks in Excel with a couple of mouse clicks?

If you keep on repeating the same thing over and over again, creating an Excel Macro is perfect for you!  It saves you time so you can do things that you like doing, like going home early 🙂

Click a button and all your tasks are done for you!

I will show you 3 different ways using which you can enable the Macro Creation and create button in Excel!

Watch our free training video on How to Create Your First Macro Button in Excel

Follow a step-by-step tutorial on how to create a button in Excel and download this Excel workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

Using ActiveX Control Buttons

Running a macro can sometimes be a tedious task. The fastest way to run a macro will be to assign a macro to a button. You can use ActiveX controls to create button in Excel.

STEP 1: Right-click anywhere on your Ribbon and select Customize the Ribbon:

3 Easy Methods to Create Button in Excel for Macros

 

STEP 2: Make sure the Customize Ribbon is selected.

See also  Free Microsoft Excel Online Course - 20+ Hours Beginner to Advanced Course

Then select the Developer option under Main Tabs.

Click OK.

3 Easy Methods to Create Button in Excel for Macros

 

STEP 3: Now go to Developer > Insert > ActiveX Controls > Button

3 Easy Methods to Create Button in Excel for Macros

Drag anywhere in the Excel spreadsheet to create button in Excel.

3 Easy Methods to Create Button in Excel for Macros

 

STEP 4: Right-click on your new button and select View Code.

3 Easy Methods to Create Button in Excel for Macros

STEP 5: Type in this code:  Range(“A6”).Value = “Hello”

What this will do is to set the value of cell A6 to Hello.

3 Easy Methods to Create Button in Excel for Macros

 

STEP 6: Time to test it out! Make sure Design Mode is deselected.

3 Easy Methods to Create Button in Excel for Macros

 

STEP 7: Click on the button now and you will see Hello pop up on your screen!

3 Easy Methods to Create Button in Excel for Macros

 

In this way, you can create button in Excel and assign a macro to it. Using VBA codes, you can also assign a different task for a different trigger like double-click single-click, right-click, etc.

You can even change the formatting of this button by right-clicking on the button and selecting the option Format Control.

3 Easy Methods to Create Button in Excel for Macros

But the only issue with this method is that the size of the button may change when you make changes to the worksheet or share it with someone else.

 

Using Shapes

Shapes provide you the flexibility to format and customize the appearance of the buttons on your Excel spreadsheet.

You can use different shapes, icons, WordArt images, and objects as per the theme of your worksheet. The formatting will also remain consistent using this method.

See also  The Easiest Ways to Extract Data from Hyperlinks in Excel

In my opinion, this is the simplest and most adaptable option to create button in Excel and run a macro.

 

Let’s look at a detailed tutorial on how we can go about this:

STEP 1: Go to Insert Tab > Select Illustrations > Under shapes dropdown > Select rectangle shape.

3 Easy Methods to Create Button in Excel for Macros

STEP 2: Right-click the shape and select Edit Text to add text to the button.

3 Easy Methods to Create Button in Excel for Macros

Or, you can double click on the button to edit text.

STEP 3: Provide a name to this button – Highlight Max Value.

3 Easy Methods to Create Button in Excel for Macros

STEP 4: Go to Developer >Visual Basic.

3 Easy Methods to Create Button in Excel for Macros

STEP 5: Type the following code in the module.

Copy Source Code:

<em>Sub HighlightMaxValue()</em>

<em>Dim cell As Range</em>
<em>For Each cell In Selection</em>

<em>'If it matches the highest value, then change the style to be Note</em>
<em>If cell = WorksheetFunction.Max(Selection) Then</em>
<em>cell.Style = "Note"</em>
<em>End If</em>
<em>Next cell</em>

<em>End Sub</em>

3 Easy Methods to Create Button in Excel for Macros

HighlightMaxValue will now be saved!

STEP 6: You can easily add a macro to this button by right-clicking on the button and select Assign Macro.

3 Easy Methods to Create Button in Excel for Macros

STEP 7: In the Assign Macro dialog box, you can select one macro (here, HighlightMaxValue) from the list saved in the Excel Workbook. Click OK.

3 Easy Methods to Create Button in Excel for Macros

This HighlightMaxValue will highlight the cell with maximum value with yellow color.

STEP 8: Select the range and then press the Excel Macro button.

See also  Attach Current Workbook into an Email Message Using Macros In Excel

3 Easy Methods to Create Button in Excel for Macros

STEP 9: Select the maximum value from the range i.e. 947 will be highlighted.

3 Easy Methods to Create Button in Excel for Macros

This is how you can easily use shapes to create button in Excel and assign a macro to it.

 

You can also customize these buttons by following the steps below:

  • Hold the Ctrl key and click on the button.
  • Go to Shape Format Tab
  • Select an appropriate option from the list – shape fill, shape outline, shape effects, text fill, text outline, text effects, size, etc.

3 Easy Methods to Create Button in Excel for Macros

To provide the button a 3-D effect – Go to Shape Format > Shape Effects > Bevel > Round.

3 Easy Methods to Create Button in Excel for Macros

This is how your 3-D Excel Macro button will look like:

3 Easy Methods to Create Button in Excel for Macros

 

You can also prevent these buttons from changing their size when you change the size of a column or row.

  • Right-click on the button and select Size and Properties.
    3 Easy Methods to Create Button in Excel for Macros
  • In the Format Shape dialog box, go to Properties and select Don’t move or size with cells.
    3 Easy Methods to Create Button in Excel for Macros

Now, even if you change the size of the rows or columns, the button will remain intact.

3 Easy Methods to Create Button in Excel for Macros

Add to Quick Access Toolbar

You can easily add a macro as a button on the Quick Access Toolbar and use it on any of the sheets present in the workbook.

STEP 1: Right-click on the arrow next to the Quick Access Toolbar and select More Commands.

3 Easy Methods to Create Button in Excel for Macros

STEP 2: In the Choose commands from list, select Macros.

See also  How to Delete Entire Row in Excel using VBA - 5 Detailed Examples

3 Easy Methods to Create Button in Excel for Macros

STEP 3: Select HighlightMaxValue from the list and click on Add>>.

3 Easy Methods to Create Button in Excel for Macros

STEP 4: Click on Modify to change the symbol of this macro.

3 Easy Methods to Create Button in Excel for Macros

STEP 5: Select an appropriate symbol and click OK.

3 Easy Methods to Create Button in Excel for Macros

STEP 6: Click OK.

3 Easy Methods to Create Button in Excel for Macros

 

Your Quick Access Toolbar will now contain the macro – HighlightMaxValue. Simply, click on this icon and your macro will run!

3 Easy Methods to Create Button in Excel for Macros

Further Learning:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, Power Query, and Macros & VBA!

101 Macros Book

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

3 Easy Methods to Create Button in Excel for Macros | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!