How to Create a Macro Button to Save & Close an Excel Workbook
A Command Button
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 the code is the 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 short cut.
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.
Adding 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.
Form Control Button
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.
Trace the Button and Name the Macro
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.
Visual Basic Code
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.
Save as Macro Enabled
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 video below.
How to Add a Macro Button That Saves and Closes an Excel Workbook
To learn more about using Excel macros for business applications I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
The Excel 2019 Bible
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.
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