We can use VBA to close workbooks. In this tutorial, we will learn how to close automatically our workbook using VBA in situations like closing all active workbooks, close workbook without saving, close workbook with a new file name and many more.
Add Macro button
We can run VBA codes from the VBA Window, but it is easier to simply click on command buttons and get the task done. Therefore, we will create Macro buttons and assign macro codes to close the workbook in different conditions.
- We will go to the Developer Tab and select Insert
- In the Drop-down menu, we will click on Form Controls
Figure 1 – Add button to use VBA close
- We can control the properties of the button by right-clicking on it and select Format Control
- Now, whenever we wish to assign the VBA codes displayed below, we can right-click and select Assign Macro. This will take us to the VBA window where we can enter our VBA code.
Figure 2- Assign Macro for VBA code to close the workbook
Using WorkBook.Close VBA to close All Workbooks
We can close all Excel workbooks at once using two methods. These methods will save all changes and automatically close the workbook.
1. The Workbook.Close VBA only closes one workbook at a time, so you have to run it repeatedly to close each workbook.
Sub Close_All_Workbooks ()
Figure 3 – ActiveWorkbook Close to close the file
2. Since the Workbook Close VBA code only works one workbook at a time, we can also use the Close_All_Workbooks_Loop to hasten the process. To use this method, we can use this code below;
Sub Close_All_Workbooks_Loop ( )
Dim myWorkbook As Workbook
For Each myWorkbook In Workbooks
Figure 4 – VBA Code to Close workbook
Excel VBA Code to Close a Workbook Without Saving Changes
We can close our current workbook without saving recent changes. To do so, we will use the VBA code below:
Sub Close_Workbook_Without_Saving_Changes ()
Figure 5 – VBA code to close without saving the workbook
Using ActiveWorkbook.Close to close an active Workbook
If we want to close an active workbook and receive prompts to save or not to save the most recent changes in our document, we can use this code below.
Sub Close_Workbook ( )
Figure 6 – VBA Workbook Close Macro
Excel VBA code to close Workbook without Prompts
We can equally close our current workbook without saving it by using the macro code below. In addition, this code would close the workbook without any prompts or alerts.
Sub Close_Workbook_Without_Prompt_1 ()
Application.DisplayAlerts = False
Figure 7 – Excel VBA Close file without prompts