Excel VBA to Close Workbook – Excelchat

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.