How to Require Excel Cells to Be Completed Before Saving or Closing
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.

- 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.
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.
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.
Go to the Developer tab on the Excel ribbon and click 'Visual Basic', or simply press ALT + F11 on your keyboard.
In the Project Explorer pane on the left side, locate your current workbook and double-click on 'ThisWorkbook' to open its code window.
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.
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."
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 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. Open WPS Spreadsheet: Launch WPS Office and open your workbook.
- 2. Enable the Developer Tab: Navigate to the Developer tab. If it's not visible, enable it from the WPS options menu.
- 3. Add the Validation Code: Click the 'Visual Basic' icon, double-click 'ThisWorkbook', and paste your BeforeSave and BeforeClose scripts.
- 4. Save as Macro-Enabled: Go to Menu > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to preserve your code.

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.




