In this article we will learn how to excel close workbook using VBA in Microsoft Excel 2010.
After your VBA macro / code has performed all the required actions, you would want the macro to either save the file or skip saving it. So here are the options you can set in the code while asking it to close the file. The Close and Open method in VBA can be used to close and open workbooks.
If you want the code to just close the file without saving the changes – then use this line at the end of your code or wherever needed -
ActiveWorkbook.Close (False) or ActiveWorkbook.Close False or ActiveWorkbook.CloseSaveChanges:=FALSE
See the code in this picture below. To copy the code, press Alt + F11 on the keyboard while ensuring that the excel sheet is open. Then right click on Microsoft Excel Objects and select Insert. Then click on Module. Copy over the code given below the picture -
Note: You can change the text ActiveWorkbook to refer to the name of the workbook or the variable you have used to identify the workbook as per your code.
If you want the code to save the changes while closing the file, then use
ActiveWorkbook.Close (True) or ActiveWorkbook.Close True or ActiveWorkbook.Close SaveChanges:=TRUE
Here is the code –
However, if you want the user to decide, then you just need to enter
Here is the code –
The user will get a popup asking if they want to save the workbook or not.
This example will close File1.xls and discards any changes that have been made to it.
If “Application.DisplayAlerts” has been set to False, you will not get any popup and the workbook will directly close without prompting to save.
This popup will appear if “Application.DisplayAlerts” is not set to False anywhere before the excel vba Activeworkbook.Close line of code.
So even if the code requires that you switch off the alerts, ensure that “Application.DisplayAlerts = True” is present before the Activeworkbook.Close line.