Open and Close Workbook in Excel Vba

In this lesson you can learn how to open and close workbook using VBA code.

How to open and close

Let's do it in Excel using VBA. You want to have macro which can open and close Excel workbook. Let's see a VBA code, which does it.

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 can open and also close your workbook. Advantage is that it will also work if a file name changed.

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.

How to prevent closing

I will guide you how to prevent closing the Excel workbook from the red close button at the top using Excel VBA.

Lets first look at the button we want to disable:

This is an inbuilt close button for the Excel application like we have for other applications but we can disable it for some specific purpose.

Sometimes the user do not want the Excel file to be modified and sometimes the user wants the file not to be closed without saving and hence it is required to disable this button.

We have to use a code in the "Workbook_BeforeClose" even in the workbook module like below:

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 condition to stop closing is the cell B1 cannot be empty.

So if we try to close it with empty cell B1:

Similarly there can be other conditions to stop the closing of the Excel file without fulfilling the condition and we can modify the same in the above code as per the requirements.