VBA & Macro Problems

How to Require an Excel Cell Before Closing Based on Another Cell's Value

John WilsonJohn Wilson Nov 2, 2026 870 views

Question details

The user needs to prevent an Excel workbook from closing if a specific cell is left blank, but this requirement should only trigger if another dependent cell contains a specific value (e.g., FERT).

Require an Excel Cell Before Closing When Another Cell Has a Specific Value
Product
Excel
Device & OS
not provided
Scenario
Enforcing data completeness and ensuring users fill out mandatory fields before saving and exiting a spreadsheet.
Observed behavior
Standard formulas like =IF() only display text alerts but cannot intercept the application's closing event. A VBA macro is strictly required to forcefully prevent the window from closing.
Before you start

Ensure you have the Developer tab enabled in your ribbon and that your application settings allow macros to run, as this solution relies on VBA code to intercept the workbook closing event.

Solution 1Recommended

Use the Workbook_BeforeClose VBA Event

This is the primary method to forcefully prevent an Excel workbook from closing based on cell conditions.

Formulas alone cannot prevent a workbook from closing. To physically stop the user from exiting the application, you must use a VBA event called Workbook_BeforeClose.

By placing a logic check in the ThisWorkbook module, Excel will run your code every time a user attempts to close the file. If the conditions are met (one cell has a specific value and the required cell is blank), the macro cancels the close command.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Access the ThisWorkbook Module

In the Project Explorer pane on the left side of the editor, locate your current workbook and double-click on 'ThisWorkbook'.

3
Select the BeforeClose Event

In the main code window, select 'Workbook' from the left drop-down menu at the top, and then select 'BeforeClose' from the right drop-down menu.

4
Input the Validation Code

Enter your conditional logic between the Private Sub and End Sub lines. For example, check if Range("F22").Value is "FERT" and Range("K22").Value is "". If both are true, use MsgBox to alert the user, use 'Cancel = True' to abort the closing process, and use Range("K22").Select to guide the user to the blank cell.

5
Save as a Macro-Enabled Workbook

Close the VBA editor and save your file as an Excel Macro-Enabled Workbook (.xlsm). If saved as a standard .xlsx, the macro will be stripped out and the validation will fail next time.

Use the Workbook_BeforeClose VBA Event
Testing Your Code: To test the script, type the trigger value into your designated cell, leave the required cell blank, and click the 'X' to close the software. The message box should appear and the file should remain open.

Enforce Cell Entry Before Closing in WPS Spreadsheet

WPS Office Spreadsheet provides excellent built-in support for VBA and macros, allowing you to easily set up Workbook_BeforeClose events without switching to expensive software.

  1. 1. Enable the Developer Tab: Open WPS Spreadsheet, go to the top ribbon, and click on the 'Developer' tab to access advanced macro tools.
  2. 2. Open the VB Editor: Click the 'VB Editor' button. In the Project pane, double-click on 'ThisWorkbook'.
  3. 3. Paste the BeforeClose Macro: Write or paste your conditional VBA code to evaluate the cell values and apply 'Cancel = True' if the requirements are unmet.
  4. 4. Save Your Work: Go to Menu > Save As, and choose 'Macro-Enabled Workbook (*.xlsm)' to preserve your new data enforcement rules.
Full support for advanced VBA macros and Developer toolsSeamlessly opens, edits, and saves Microsoft Excel (.xlsm) formatsFree to use with a lightweight installation packageFamiliar user interface matching modern spreadsheet standards
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't a standard IF formula prevent the workbook from closing?

Standard Excel formulas like =IF() evaluate data on the spreadsheet level to calculate values or display text. They do not have the capability to interact with application-level events, such as intercepting the window's close command. That type of action requires VBA programming.

How do I apply this VBA close restriction to multiple cells?

You can expand your VBA code within the BeforeClose event by using multiple 'If...Then' statements or 'Select Case' logic. Simply add additional conditions to check different ranges, and apply 'Cancel = True' if any of your specified cell pairs fail the completeness check.

Do I need to save my file in a specific format after adding this code?

Yes. Any file containing a VBA macro must be saved as an Excel Macro-Enabled Workbook (.xlsm) or an Excel Binary Workbook (.xlsb). If you save it as a standard .xlsx file, Excel will automatically remove the macro code upon saving.

Can users bypass the BeforeClose macro?

Yes. If a user opens the file and disables macros when prompted by the security warning, the BeforeClose event will not trigger. To mitigate this, developers often employ a 'Macro Hiding' technique, where all functional sheets are hidden by default and only unhidden via VBA when macros are enabled.