In this lesson, you will learn how to open and close workbooks using VBA code in Excel. Additionally, you’ll explore how to prevent the closure of a workbook under certain conditions.

Opening and Closing Workbooks Using VBA

Below is a VBA code that demonstrates how to open and close a workbook:

Sub OpenCloseWorkbook()
Dim MonthlyWB As Variant
Dim FileName As String
MonthlyWB = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
Workbooks.Open MonthlyWB
FileName = ActiveWorkbook.Name
Workbooks(FileName).Close
End Sub

This macro allows you to open and close an Excel workbook. One advantage of this code is that it can handle cases where the file name has changed.

Closing a Workbook with Save Changes

To close a workbook in Excel VBA and save the changes you can use the following code:

Workbooks(FileName).Close savechanges:=True

The savechanges argument is set to True, which means that any changes made to the workbook will be saved before it is closed. If you set savechanges to False, the workbook will be closed without saving any changes.

Preventing Closure of a Workbook

You can use VBA to prevent the closure of an Excel workbook under certain conditions. For instance, you might want to ensure that a specific cell is not left empty before allowing the workbook to be closed. Here’s an example code that does this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("Sheet1").Range("B1").Value = "" Then
Cancel = True
MsgBox "Cell B1 cannot be blank"
Else
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub


In this code, the Workbook_BeforeClose event is used to trigger actions before closing the workbook. It checks if cell B1 in “Sheet1” is empty. If it is, the workbook closure is canceled (Cancel = True), and a message is displayed to inform the user that “Cell B1 cannot be blank.” Otherwise, if the condition is met, the workbook is closed with changes saved.

You can modify the conditions in this code to suit your specific requirements for preventing workbook closure.

Related posts:

How to Use Group Box in Excel Do While Loop in Excel Vba How to Use VLOOKUP with VBA