Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
What Is a Macro Button?
A macro button is a button that is created from macro code or from recording a macro in Microsoft Excel. When macro code is created (or the macro is recorded), it can be assigned to a button. Once assigned to the button, a macro process can be repeated by clicking on the button that it is assigned to.
Creating a save & close button is a great way to save some precious time. Buttons can be placed in more convenient areas of worksheets to allow you to run processes together so you can speed up. Let's look at exactly how to create this shortcut.
Add the Developer Tab
Make sure you have the developer tab visible in the Excel Ribbon for this tutorial. If you need to add the developer tab to the ribbon, click here to find out how. The screenshot below shows the default location for the developer tab.
Inserting a Button
The first step to creating a save and close macro button is inserting the button itself. To start this task, click on the developer tab, then click on insert in the controls section. A few dozen icons will appear. In the form controls section, click on the icon that is in the first row and in the first column.
Trace the Button
Trace a square or rectangle of the size that you want the button to be with your cursor in your Excel worksheet. A new window titled assign macro will appear. Rename the macro if you wish, then click on new. A new window will appear where you can insert some code to tell the button what to do when it is pressed.
Write the Code
So now you have a chance to practice writing some code. The text below can be copied and pasted right into the module.
The first and last line of the code will already exist in the module. I suggest that you delete these and paste the four lines shown above directly into the module. Once you enter the code, be sure to click the save button.
Save the Workbook
If the Excel workbook is not already saved, you will be prompted to do so as a result of saving the module in the last step. When saving the workbook, make sure that you save the workbook as a Macro-Enabled workbook.
Test the Button
After the workbook has been saved as a macro-enabled workbook the button can be tested. To create this test, make sure that the button is deselected, then add some text to a cell.
Next, click the button to see if it operates properly. Excel should close without and prompts to ask you if you would like to save the workbook. When you open the workbook again make sure that the text that you typed saved.
Now that your button works, you may want to label it so you and others will know what its function is. To change the name of the button right-click on it then click on the edit text option in the drop-down menu. This will put your cursor in the button to make changes.
Other properties of the button can be changed by right-clicking on it and selecting format control. Here the text color, style, and size can also be changed. Other properties such as appearance can be changed within form control as well.
For further assistance on how to create a save and close button in Microsoft Excel, try the video below.
Crowder, J. (2019, December 3). Retrieved January 9, 2020, from https://youtu.be/LzFQpKwAIfM.
Crowder, J. (2020, January 1). How to Add the Developer Tab in Excel. Retrieved January 1, 2020, from https://youtu.be/nskuG6pK5ig.
- How to Create a Button in Microsoft Excel That Opens a Calculator
Shows a Microsoft Excel user how to create a button that opens up the Windows pre-installed calculator application from an Excel spreadsheet.
- How to Add the Developer Tab in MS Excel 2016
This article shows you how to navigate through the MS Excel options menu to enable visibility of the developer tab within the Excel main menu.
- Create a Macro Button That Will Clear All of Your Work Within an Excel Worksheet
Shows a Microsoft Excel user how create a button and record a macro that will clear a workbook when the button is clicked.
- Create a Macro Button in MS Excel 2016 to Filter Data
The article will show you how to create a set of buttons allowing you to filter a table within a spreadsheet and revert to your original view quickly. A document template for the task will be provided.
To learn more about developing in Excel, I recommend taking a look at the book Microsoft Excel 2019 VBA and Macros (Business Skills) by Bill Jelen to enhance your skills.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2018 Joshua Crowder