Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

VBA Code for Save Button in Excel (4 Variants)

Often users encounter lengthy Excel sheets that need frequent saving. Therefore, using VBA Code for Save Button in Excel is a handy way out. There are multiple variants of VBA Macro to do the job.

Let’s say we have a dataset that needs frequent saving as shown below.

Dataset-VBA Code for Save Button in Excel

In this article, we demonstrate multiple variants of VBA code for Save Button in Excel.


Download Excel Workbook


How to Insert a Macro Button and Assign a Macro?

Before creating a VBA-Enabled Save Button, users first need to insert a Command Button and assign a Macro to it. Follow the below sections to insert a Command Button and assign a Macro.


Inserting a Command Button

🔼 Go to the Developer tab > Click on Insert (in the Controls section) > Click on a Command Button (under Active X Controls).

Command Button-VBA Code for Save Button in Excel

🔼 Right-click on the inserted Command Button. The Context Menu appears. From the Context Menu, select Properties.

Properties

🔼 In the Properties window, edit necessary items as needed as depicted in the below image.

Modified Properties


Assigning a Macro to the Command Button

🔼 After inserting the Command Button and modifying its properties, right-click on it. Select the View Code option from the Context Menu.

Assigning Macro-VBA Code for Save Button in Excel

🔼 In a moment, Excel brings Microsoft Visual Basic Window with Command Button code space.

Microsoft Visual Basic

Type the preferred macro in the offered space then click on the Save Button to run it.


4 Variants of VBA Code for Save Button in Excel

Variant 1: VBA Code for File Saving Then Quit Excel

For saving final Excel file versions and then quitting the application users can use this macro variant. Follow the described steps to do so.

Step 1: Paste the following macro under the Command Button.

Private Sub CommandButton1_Click()
Application.Quit
ThisWorkbook.Save
End Sub

Macro-1-VBA Code for Save Button in Excel

➤ in the code,

1 – start the macro procedure by declaring the Sub name. And it’s a Command Button.

2 – ask Excel to save the workbook content then quit the application.

Step 2: Click on the inserted Save Button. As a result, Excel saves the modified content and then terminates the active Excel window.

Click on Save Button

Read More: How to Save VBA Code in Excel (3 Suitable Methods)


Variant 2: Macro Code for a Save As Button

In case, users want to manually select the saved location and provide the file name, they can use this variant. This variant allows users to save each modification separately whenever they click on the Save Button.

Step 1: Paste the below code in the Command Button space.

Private Sub CommandButton1_Click()
With Application.FileDialog(msoFileDialogSaveAs)
.Title = ("Provide a File Name then Select File Type & Location")
.ButtonName = "Save As"
.Show
End With
End Sub

Macro-2-VBA Code for Save Button in Excel

➤ The code’s sections,

1 – initiate the macro procedure declaring the Sub name.

2 – fetch the Save As dialog box using the Application With statement.

Step 2: Return to the worksheet then click on the Save Button.

Click on Save Button

Step 3: Excel brings the Save As dialog box saying, “Provide a File Name then Select File Type & Location”. Click on Save As to save the file in the desired location with a preferred name.

Save As window

Read More: Excel Macro to Save as PDF (5 Suitable Examples)


Similar Readings


Variant 3: Code for Saving File with Custom Name in a Fixed Location

Sometimes, users want a custom name for their about to save file and a fixed location. This macro offers those two options whenever users save an excel file using the Save Button.

Step 1: Write the below macro under the Command Button space.

Private Sub CommandButton1_Click()
Dim mFileName As String
mFileName = Range("B5").Value
ActiveWorkbook.SaveAs (mFileName)
End Sub

Macro-3-VBA Code for Save Button in Excel

➤ From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variable as String. Then assign the variable name to the B5 cell entry. At last, save the entire workbook.

Step 2: Click on the Save Button. In a moment, Excel changes the file name to the value of the B5 cell (i.e., Sales on 3rd June’22).

Click on Save Button

🔺 This macro automatically saves the Excel file in the Device’s Document directory. Go through the directory and you find the saved file as shown below.

Saved directory

Read More: Excel Macro to Save as PDF with Filename from Cell Value (2 Examples)


Variant 4: Saving File with Given Name and Location Using Button

To assign a specific name and location, users can modify their macros similar to this one. This macro holds a specific name and location for each save using the Save Button of an Excel file.

Step 1: Use the below code in the Command Button space.

Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs _
FileName:="C:\Users\maruf\Desktop\Softeko\New folder\Save Button"
End Sub

Macro-4-VBA Code for Save Button in Excel

➤ The above image has code sections,

1 – take forward the macro by setting the Sub name.

2 – save the Excel file in the given directory.

Step 2: Again, click on the Save Button to execute the macro.

Click on Save Button

🔺 Execution of the macro saves the file in the specified directory as depicted in the below screenshot.

Saved file

You can change the directory used in the macro to save wherever you wish to save the file.

Read More: Excel VBA: Choose Location and Save as PDF


Conclusion

In this article, we demonstrate a couple of variants of VBA code for Save Button in Excel. Usually, it’s inefficient to save a small or less sensitive excel file using Save Button. However, it’s handy to save Excel files that require frequent saving using a Save Button. Hope these macros help you to overcome your situation. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo