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. 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.

Macro Code (VBA Code)

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, 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

To implement this macro, you need to copy and paste it into the Workbook BeforeClose event code window. Placing the macro there 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.Workbook BeforeClose Event
  5. Type or paste the code in the newly created module, modifying the sheet name or target cell (if necessary).

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>

8 comments
  1. MA
    Matt

    Hi there, this is exactly what I am looking for but I would like to apply it only to the active worksheet. Is this possible? Thank you.

  2. CJ
    CHETAN JOSHI

    Working for me.. save you excel workbook in macro mode. do this.. file menu---> save as ---> file name below select --.. save as type "Excel Macro-Enabled Workbook". do not save you file as .xls or .xlms macro file extension is ".xlsm"

More comments