How to prevent closing Excel?

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

The button

Lets first look at the button we want to disable:

vba prevent close button

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.

The code

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.

prevent close vba code

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

cell cannot be blank vba

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.