VBA & Macro Problems

How to Require Excel Cells to Be Completed Before Saving or Closing

Ayan MasoodAyan Masood Oct 29, 2026 870 views

Question details

The user wants to enforce a rule where specific mandatory cells (e.g., marked with an asterisk) must be filled out before an Excel workbook can be saved or closed.

Require Excel Cells to Be Completed Before Saving or Closing
Product
Excel
Device & OS
not provided
Scenario
Creating standardized forms, such as a Supplier Registration Form, where specific fields are mandatory to ensure complete data collection before submission.
Observed behavior
By default, Excel allows users to save or close workbooks at any time, even if essential data fields are left completely blank.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) so the VBA code can execute. Identify the exact sheet name and cell references for the mandatory fields before writing the script.

Solution 1Recommended

Use VBA Workbook Events to Validate Required Cells

Add a VBA macro to the 'ThisWorkbook' module to intercept save and close actions, warning the user and cancelling the action if mandatory cells are empty.

By utilizing the Workbook_BeforeSave and Workbook_BeforeClose events, you can create a validation check. When a user triggers a save or close command, the macro inspects the specified worksheet (e.g., 'Supplier Registration Form'). If any predefined required cell is blank, the macro displays a message box with instructions and stops the workbook from saving or closing.

1
Open the VBA Editor

Go to the Developer tab on the Excel ribbon and click 'Visual Basic', or simply press ALT + F11 on your keyboard.

2
Access ThisWorkbook

In the Project Explorer pane on the left side, locate your current workbook and double-click on 'ThisWorkbook' to open its code window.

3
Insert the BeforeSave Code

Select 'Workbook' from the left dropdown above the code window, and 'BeforeSave' from the right dropdown. Write an If statement to check if your required cells on the 'Supplier Registration Form' sheet are empty.

4
Cancel the Save Action

Inside the If statement, add 'Cancel = True' and a 'MsgBox' function to alert the user. For example: MsgBox "Please complete all cells marked with an asterisk before saving."

5
Repeat for BeforeClose

Similarly, select the 'BeforeClose' event from the top right dropdown and paste the same validation logic, ensuring 'Cancel = True' is triggered to prevent the window from closing.

Use VBA Workbook Events to Validate Required Cells
Macro Security: End users must have macros enabled in their Excel settings for this validation to trigger. If macros are disabled, the workbook will save and close normally without checking the cells.
Advanced Macro Support

Use WPS Spreadsheet to Create and Manage Macro-Enabled Forms

WPS Spreadsheet provides excellent support for VBA macros, allowing you to easily enforce data entry rules like making cells mandatory before saving. It offers a seamless environment for developing, testing, and sharing your macro-enabled forms.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook.
  2. 2. Enable the Developer Tab: Navigate to the Developer tab. If it's not visible, enable it from the WPS options menu.
  3. 3. Add the Validation Code: Click the 'Visual Basic' icon, double-click 'ThisWorkbook', and paste your BeforeSave and BeforeClose scripts.
  4. 4. Save as Macro-Enabled: Go to Menu > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to preserve your code.
Full support for running and editing VBA (Visual Basic for Applications) macros.Seamless compatibility with Microsoft Excel .xlsm and .xlsb formats.Intuitive Developer tab for easy macro creation and code editing.Lightweight, cost-effective, and highly compatible Office suite.
microsoft office alternative - wps office

Frequently Asked Questions

How do I bypass the macro if I am the administrator needing to save an incomplete form?

You can temporarily disable VBA events. Open the VBA Editor, open the Immediate Window (CTRL + G), type 'Application.EnableEvents = False', and press Enter. Make your changes, save the file, and then turn events back on by typing 'Application.EnableEvents = True'.

What happens if the user disables macros upon opening the file?

If macros are disabled, the VBA validation code will not execute, and the user can save or close the workbook with empty cells. To mitigate this, developers often hide all data sheets by default and use a macro to unhide them only when macros are enabled.

Can I highlight the empty cells instead of just showing a message box?

Yes. Within your VBA script, before cancelling the save, you can add code like 'Range("B2").Interior.Color = vbRed' to highlight the empty required cells, making it visually obvious to the user what they missed.

Will this VBA validation code work in WPS Office?

Yes, WPS Office fully supports VBA macros. As long as you are using a version of WPS Office with VBA components installed and macros are enabled in the settings, the BeforeSave and BeforeClose events will function exactly as they do in Excel.