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.
In this article, we demonstrate multiple variants of VBA code for Save Button in Excel.
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).
🔼 Right-click on the inserted Command Button. The Context Menu appears. From the Context Menu, select Properties.
🔼 In the Properties window, edit necessary items as needed as depicted in the below image.
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.
🔼 In a moment, Excel brings Microsoft Visual Basic Window with Command Button code space.
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
➤ 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.
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
➤ 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.
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.
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
➤ 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).
🔺 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.
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
➤ 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.
🔺 Execution of the macro saves the file in the specified directory as depicted in the below screenshot.
You can change the directory used in the macro to save wherever you wish to save the file.
Download Excel Workbook
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
- Text Alignment with VBA Command Button in Excel
- How to Use VBA Code for Submit Button in Excel
- How to Make a Calculate Button in Excel
- How to Create a Stopwatch in Excel
