Macro 14: Preventing the Workbook from Closing Until a Cell Is Populated
There are times when you don’t want a user closing out a workbook without entering a specific piece
of data. In these situations, you want Excel to deny the user the ability to close the workbook until
the target cell is filled in. This is where this macro comes in.
How it 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 saves
and closes.
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: Blank: cancel the Close and tell the user
Cancel = True
MsgBox “Cell C7 cannot be blank”
‘Step 3: Not Blank; Save and Close
Else
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub
1. Step 1 checks to see whether C7 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 C7 is not blank, the workbook saves and closes.
How to use it
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 the 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 in
order to see all the sheets.
3. Click ThisWorkbook.
4. Select the BeforeClose event in the Event drop-down list (see Figure 2-9).
5. Type or paste the code in the newly created module.
Figure 2-9: Type or paste your code in the Workbook_BeforeClose event code window.
