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

• GetOpenFilename displays a file picker.
• Workbooks.Open opens the file.
• Workbooks(…​).Close closes by name, accommodating renamed files.

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


• Workbook_BeforeClose fires when the user attempts to close.
• Setting Cancel = True halts closure.
• Customize the condition (e.g., other cells, multiple checks) before allowing close.

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

Customizing Closure Conditions

Modify the If block to enforce other rules:

If Sheets("Summary").Range("A1").Value < 1000 _
Or Sheets("Config").Range("C5").Value = "Locked" Then
Cancel = True
MsgBox "Cannot close until targets met or unlocked.", vbCritical
Else
Cancel = False
End If

• Combine multiple checks with And/Or.
• Use descriptive message boxes to guide users.

Abstract open/close routines for reuse across projects.

Related posts:

For Each Next Loop in Excel Vba Do While Loop in Excel Vba How to Write to a Cell in Excel Vba