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 ------------------ 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.
- Step 1 checks to see whether A1 is blank.
- 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).
- If cell A1 is not blank, the workbook saves and closes.
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment
'------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?
The following steps teach you how to put VBA code into a Standard Module:
- Activate the Visual Basic Editor by pressing ALT + F11.
- Right-click the project/workbook name in the Project Window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA