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:
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.
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.
Note: Keep in mind that these methods will only prevent accidental closing of the workbook or worksheet and can still be bypassed by users who have access to the Visual Basic Editor (VBE) or know the password for the protected workbook structure.