Excel Macro: Prevent the Workbook Closing If a Cell is Empty

Sometimes you may not want a user closing out a workbook without entering a specific piece of data. In these situations, you want Excel to prevent the user from closing or saving a file if certain cells are empty. If you want to to make sure the user has entered all the required fields before saving and closing the program. This is where this macro comes in.

Prevent the Workbook Closing If a Cell is Empty

Copy and paste the following VBA code into ThisWorkbook:

'------------------ ThisWorkbook ------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Check to see if Cell A1 is blank
    If Sheets("Sheet1").Range("A1").Value = "" Then

'Step 2: Blank: cancel the Close and tell the user
        Cancel = True
        MsgBox "Cell A1 can not be blank"
'Step 3: Not Blank; Save and Close
    Else
        ActiveWorkbook.Close SaveChanges:=True
    End If
End Sub

How This Macro Works

This code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. This macro checks to see if the target cell (Cell A1 of Sheet1, in this case) is empty. If it is empty, the close process is cancelled. If A1 is not empty, the workbook saves and closes.

1. Step 1 checks to see whether A1 is blank.
2. If it is blank, Step 2 takes effect, cancelling the close process. This is done by passing True to the Cancel Boolean. Step 2 also activates a message box notifying the user of their stupidity (well, it’s not quite that harsh, really).
3. If cell A1 is not blank, the workbook saves and closes.

How to Use This Macro