Prevent Closing Excel: Workbook Protection

How to prevent closing Excel

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.

Template

Further reading: 
Basic concepts 
Getting started with Excel
Cell References