spot_img

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.

Lets first look at the button we want to disable:

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.

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

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

You can download the Template here – Download
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell