Excel VBA: Save and Close Workbook Without Prompt

Some Excel files need frequent saving and closing for various reasons, where having to respond to an Excel prompt each time is an unnecessary distraction. Excel Prompts are confirmation windows that seek user approval before saving files, closing Excel or making changes in workbooks.

This article discusses the reasons for Excel displaying prompts, and demonstrates how to use macros in Excel VBA to save and close workbooks without prompts.

Excel Prompts for Saving and Closing a Workbook

After making changes to Excel file, if you attempt to close the workbook without saving it first, Excel displays a Prompt window saying, “Want to save your changes to…”.

Similar prompts are displayed when users try to save files in different formats, such as xlsx to xlsm.

3 Examples of Using Excel VBA to Save and Close Workbooks Without the Prompt

To apply a VBA macro, we need to insert a Module in the Microsoft Visual Basic window, and save the workbook format as xlsm to be able to run the macro.

Steps:

Press ALT+F11 or go to the Developer tab > Visual Basic to open Microsoft Visual Basic.
In the window, click on Insert > Module to insert a module.

Save the Excel workbook as an Excel Macro-Enabled Workbook.

Example 1 – Save and Close a Workbook Without a Prompt in an Assigned Location

A couple of lines of macro code can save and close a workbook in the assigned location without the Excel prompts.

Open a new module using the method described above.
Paste the following macro into the module:

Sub Save_Close_without_Prompt()
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Users\maruf\Desktop\Softeko\Save and Close without Prompt", 52
ActiveWorkbook.Close SaveChanges:=True
End Sub

Macro Explanation

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – the Application.DisplayAlerts statement is set to False to prevent the Save and Close Excel prompts.

3 – ThisWorkbook.SaveAs takes a Path to save the changed file in a specific format (i.e., xlsm=52).

4 – the ActiveWorkbook and SaveChanges statements execute the Close and Save Changes commands respectively.

Go to Run > Click Run Sub/UserForm (or press F5) to run the macro.

Excel takes a moment, then saves the changes and closes the active workbook. Only the Excel application with no open workbook remains.

Click on Close to exit Excel.

Read More: Excel VBA: Save and Close Workbook

Example 2 – Conditionally Saving and Closing a Workbook Without the Prompt After Making Changes

This code allows you to save an Excel file repeatedly after making changes without the prompts.

Enter the following lines in a newly opened module:

Sub Save_Changes_Close_WorkBook()
ActiveWorkbook.Close savechanges:=True
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If
End Sub

Macro Explanation

1 – declares the Sub name.

2 – the ActiveWorkbook.Close statement closes the workbook, and the SaveChanges statement saves the changes.

3 – performs a condition using the VBA IF function to save the current workbook.

Repeat Example 1’s steps to run the macro and exit from Excel.

Example 3 – Saving and Closing a Specific Workbook Without a Prompt

To save and close a specific workbook without a prompt, use this macro.

Insert the macro code below into a new module:

Sub Close_Save_Specific_Workbook()
Workbooks("Save and Close without Prompt.xlsm").Close SaveChanges:=True
End Sub

Macro Explanation

1 – begin the macro code by declaring the VBA Macro Code’s Sub name.

2 – the VBA Workbooks property takes the Close and Save Changes command of a specific Excel workbook.

Go through Example 1’s steps to close and save the workbook.

Checking Saved Files for Confirmation

Users open the desired Excel files and make changes, then execute the macro to save and close the workbook. However, sometimes users need to cross check the outcomes.