An Excel Macro to Prevent Workbooks from Closing
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.
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 Else 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:
Activate Visual Basic Editor by pressing Alt+F11.
In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
In the Event drop-down list, select the BeforeClose event.
Type or paste the code in the newly created module.Enter your code in the Workbook BeforeClose event.