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 ()
Workbooks.Close
End Sub
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
myWorkbook.Close
Next myWorkbook
End Sub.
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 ()
ActiveWorkbook.Close SaveChanges:=False
End Sub
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 ( )
ActiveWorkbook.Close
End Sub
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
ActiveWorkbook.Close
End Sub
Figure 7 – Excel VBA Close file without prompts
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment