Bottom Line: Learn how to create macro buttons in a worksheet that run VBA code when clicked. Includes how to format buttons and prevent them from moving and resizing on the sheet.

Skill Level: Intermediate

Download the Excel File

Here's the Excel workbook that I use in the video so you can follow along and practice what you're learning.

Create Macro Buttons.xlsm (33.1 KB)

Making User-Friendly Macro Buttons in Your Spreadsheets

Macros make life easier. Buttons make life easier. It's only natural to marry the two and make the processes you repeatedly have to perform as simple as possible.

You can literally run through a hundred steps with just one small click using macros and buttons–which, of course, is both empowering and gratifying.

Kristen, a member of our VBA Pro Course, loves to add buttons to her spreadsheets for this reason.

Kristen Macro Buttons Testimonial Video
Click here to hear Kristen's story with learning VBA

You can watch the entire video to learn more about Kristen's story with learning VBA, and how she uses macro buttons.

For this tutorial, we're using macros that have already been created. If you don't know how to create macros yet, then checkout one of my free webinars to learn the basics getting started with macros & VBA.

So let's take a look at how to create a button and assign a macro to it.

3 Types of Macro Buttons for Worksheets

In this post I explain three different types of buttons you can place on a worksheet.

  1. Shapes
  2. Form Control Buttons
  3. ActiveX Control Buttons

We'll start with my favorite…

1. Using Shapes to Create Macro Buttons

In my opinion, the best way to create macro buttons is to use shapes. Shapes give us the most styling/formatting options, and we can make them look like buttons you click every day on modern web pages.

Below, we will create the button on a worksheet and assign a macro to it. Here are the steps to create the macro button:

1. Draw a shape on the sheet (Insert tab > Shapes drop-down > Rectangle shape).

Use a Shape for a Macro Button VBA Excel

2. Add text to the shape (Right-click > Edit Text | or double-click in the shape).

Draw the macro button shape and style it

3. Assign the macro (Right-click the border of the shape > Assign Macro…)

Right-click Assign Macro Excel Shape

4. Select the macro from the list. It's important to note that the macro should be selected from the This Workbook list so that when you share the workbook with other users, the macro will be shared as well.

If you assign the macro from another workbook, the button will not work for the people you share the workbook with.

Assign Macro Window for Macro Button Shape

5. Press OK.

When you hover your mouse over the shape it will now turn into the hand pointer cursor.  Clicking the shape will run the macro.

Left Click Shape to Run Macro - Ctrl to Select Edit Shape

That's it! That's how you assign a macro to a button that you've created right on the worksheet.

Modifying the Button

If you need to select the shape to change its look or move it, hold the Ctrl key while clicking the shape.  This will prevent the macro from running and allow you to modify the shape. Excel offers a ton of options on the Format tab for changing the shape style, shape effects, font color or size, etc.

Preventing the Button from Resizing with Cell Changes

Depending on your settings, it's likely that when you change the size of the column or row that your button is on, the button size will change as well. If you don't want that to happen, you can easily adjust the settings to keep the button static.

First, just right click on the button and choose Size and Properties…

Change size and properties of macro button

This will bring up the Format Shape pane. There are options for you to choose from in the Properties section, depending on whether or not you want the button to move or resize when changes are made to your sheet.

Format Shape Pane with options to keep the macro button static

2. Using Form Controls to Create Macro Buttons

The next option for macro buttons is Form Controls. They are essentially the same in terms of set-up.

The difference between Controls and Shapes is the styling.

Form Control buttons do give the appearance of being pressed when you click on them, which is kind of cool. However, they unfortunately look like a button you'd find on an early version of Windows.

Form Control Buttons

We cannot change the color of the button either. The only formatting options are for the font type, size, and color.

Insert a Form Control Button

To add a Form Control button, just go to the Developer tab and click on the Insert button. A drop-down gives you options under Form Controls.

Form Controls menu can be used to make macro buttons

3. Using ActiveX Controls for Macro Buttons

The last option is ActiveX Control buttons.

ActiveX Menu can be used to create macro buttons

The ActiveX Control buttons do have more formatting options and you can change the button color.

They also require you to add an event macro in the sheet module that will run when the button is clicked. This is done by right-clicking the button and choosing View Code.

You can add other event triggers like double-click. This could perform a different action/macro when the button is double-clicked.

However, I don't recommend the ActiveX Control buttons. In my experience they are glitchy in terms of consistent appearance. They can end up really big or really small when changes are made to the sheet or when the file is shared back and forth between users.

Placing Buttons in the Ribbon or Toolbar Instead

Another option is to place your macro buttons on any of the tabs in the Excel Ribbon or Quick Access Toolbar.

Create the Personal Macro Workbook and Add Buttons to the Ribbon

This is video #3 in my 4-part video series on the Personal Macro Workbook. This technique is best for macros that you will only run on your computer because the ribbon button customizations do not travel with the workbook.

If you do want the ribbon buttons to travel with the workbook then you can use XML code to customize the ribbon. I'll write an article on that in the future, and I do cover extensively in my VBA Pro Course, along with how to create add-ins.

Related Tutorials

I have a couple other posts for you to check out on this topic. Take a few minutes to give them a look!

Conclusion

My overall recommendation is to keep it simple and stick to using shapes for macro buttons. They give you the most formatting options, and are easy to setup and modify.

What will you create macro buttons for? I'd love to hear how you plan to put these buttons to work. If you have any comments or questions about running a macro button that you've created, please leave a comment below.

Thank you! 🙂

9 comments

Your email address will not be published. Required fields are marked *

  • So VERY helpful – thank you so much for sharing!!! This is a great option for files I have to share with others! I have just saved all macros in Personal so will be doing some copying to individual worksheets now for those others may need to use.

  • Hi Jon!

    FoA thank you so much for sharing valuable tips for benefits of others.

    I have posted a comment yesterday but doesn’t seem to appear here so I’m posting a new one. I hope you don’t mind.

    I need assistance to create and assign a macro to a button to lock a worksheet.

    I have a worksheet that needs to be filled by my users. The worksheet has some cells that are unlocked for entering numbers. Once users completed entering the numbers I want them to be able to lock the cells using a lock button which I have included on the worksheet so that when I received the worksheet from them I won’t be able to edit their worksheet.

    Thanking you in advance for your kind assistance.

  • Hi Jon!

    Excellent video you got there.

    Can you help me how to create and assign a macro to a ‘Lock Worksheet’ button. The purpose is that when a user completed filling the form he can lock the worksheet so that other people who receive his worksheet wouldn’t be able to edit the worksheet.

    Thanking you in advance.

  • I watched the videos Excel & VBA got them recognized in your email. I like some ideas of what they created and how did they know what is needed in VBA. I can not seem to connect the idea and how to use VBA. Learning just to learn is not fun. I need to know how to apply. If you can give examples.

  • Fantastic Jon, I was using the Macro Buttons all the time, however, I wasn’t aware of protecting the buttons from being resized. Excellent find for me!

    Was watching from my workplace, already implemented the new changes in my report.

    Keep up the good work Jon!

  • Jon, I’m about to go on STD for knee surgery and adding buttons with macros to my more complicated spreadsheets will keep me from fielding calls while I’m off. Thank you for your great ideas!!

    • Hi Lory,
      I wish you all the best with the surgery and hope for a speedy recovery.

      And yes, macros and buttons really help me take more relaxing vacations and time off. So much easier than teaching someone a complicated process that you are deeply familiar with… 🙂 Thanks again for your support.

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly