How to Write a Macro to Save as

Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.

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.

Created by Joshua Crowder

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.

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.

Created by Joshua Crowder

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

Created by Joshua Crowder

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

Created by Joshua Crowder

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.

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"

Created by Joshua Crowder

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

Created by Joshua Crowder

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

Created by Joshua Crowder

Additional Resource

For further assistance on how to create a save and close button in Microsoft Excel, try the video below.

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

• 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

How to Write a Macro to Save as

Source: https://turbofuture.com/computers/How-to-Create-a-Macro-Button-to-Save-Close-an-Excel-Workbook

Share this post