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

Updated on December 3, 2019
Joshua Crowder profile image

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.

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.

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

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

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

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

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.

Questions & Answers

    © 2018 Joshua Crowder

    Comments

      0 of 8192 characters used
      Post Comment

      No comments yet.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
      ClickscoThis is a data management platform studying reader behavior (Privacy Policy)