How to Create a Macro Button to Save & Close an Excel Workbook - TurboFuture - Technology
Updated date:

How to Create a Macro Button to Save & Close an Excel Workbook

Joshua has work experience in manufacturing, distribution, and aerospace. He received his BBA in accounting from Kent State University.

A Command Button

Macros can be created and attached to buttons for various operations. Automating long tasks can save a vast amount of time when those tasks prove to be repetitive.

Macros can be created and attached to buttons for various operations. Automating long tasks can save a vast amount of time when those tasks prove to be repetitive.

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

Developer Tab

The developer tab is not displayed in your Excel program by default. Adding the developer tab opens the door to working with macros, XML, and add-ins.

The developer tab is not displayed in your Excel program by default. Adding the developer tab opens the door to working with macros, XML, and add-ins.

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

how-to-create-a-macro-button-to-save-close-an-excel-workbook

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.

Sub Button2_Click()
Application.Quit
ThisWorkbook.Save
End Sub

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

The first line of code "Sub Button2_Click()" initiates the task. Once the button is clicked, the application Quits "Application.Quit" and the prompt is avoided because the workbook is saved "ThisWorkbook.Save."  Finally, the module ends "End Sub"

The first line of code "Sub Button2_Click()" initiates the task. Once the button is clicked, the application Quits "Application.Quit" and the prompt is avoided because the workbook is saved "ThisWorkbook.Save." Finally, the module ends "End Sub"

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

how-to-create-a-macro-button-to-save-close-an-excel-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.

Button Design

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.

Edit Text

how-to-create-a-macro-button-to-save-close-an-excel-workbook

Additional Resource

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

References

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.

Related Articles

Create a Macro Button in MS Excel 2016 to Filter Data

Create a Macro Button That Will Clear All of Your Work Within an Excel Worksheet

How to Add the Developer Tab in MS Excel 2016

How to Create a Button in Microsoft Excel That Opens a Calculator

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