An Excel Macro to Prevent Workbooks from Closing

By Michael Alexander

There are times when you don’t want a user closing out an Excel workbook without entering a specific piece of data. In these situations, it would be useful to deny the user the ability to close the workbook until the target cell is filled in (as shown here). This is where this nifty macro comes in.

Prevent closing until a specific cell is populated.
Prevent closing until a specific cell is populated.

How the 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 C7, in this case) is empty. If it is empty, the close process is cancelled. If C7 is not empty, the workbook is saved and closed:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Check to see if cell C7 is blank
If Sheets("Sheet1").Range("C7").Value = " Then
'Step 2: If cell is blank, cancel the close and tell user
    Cancel = True
    MsgBox "Cell C7 cannot be blank"
'Step 3: If cell is not blank, save and close
    ActiveWorkbook.Close SaveChanges:=True
End If
End Sub

Step 1 checks to see whether C7 is blank.

If C7 is blank, Step 2 takes effect, cancelling the close process by passing True to the Cancel Boolean. Step 2 also activates a message box notifying the user of his or her stupidity (well, it’s not quite that harsh, really).

In Step 3, if cell C7 is not blank, the workbook is saved and closed.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:

  1. Activate Visual Basic Editor by pressing Alt+F11.

  2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.

  3. Click ThisWorkbook.

  4. In the Event drop-down list, select the BeforeClose event.

  5. Type or paste the code in the newly created module.

    Enter your code in the Workbook BeforeClose event.
    Enter your code in the Workbook BeforeClose event.