How to Make Required Excel Cells Mandatory Before Saving
Question details
The user wants to ensure that specific required cells in partially completed rows are filled out before the workbook can be saved.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- When users fill out a shared tracker or form, they often leave critical fields blank. The goal is to enforce data entry for active rows without blocking the save function for completely blank new rows.
- Observed behavior
- By default, Excel allows saving regardless of whether cells are blank. The user needs a custom validation method to intercept the save action, warn the user, and cancel the save if required data is missing.
Ensure you are prepared to save your workbook in a Macro-Enabled format (.xlsm), as standard Excel files (.xlsx) will automatically delete your VBA code upon closing.
Use the Workbook_BeforeSave VBA Event
Add a customized macro to the ThisWorkbook module that evaluates rows and blocks the save action if required fields are blank.
Since standard Data Validation cannot prevent a user from saving a file with empty cells, VBA is required to intercept the save action. By using the Workbook_BeforeSave event, you can command Excel to evaluate specific cell ranges right before the save executes.
Open your workbook and press the 'ALT + F11' keys simultaneously to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left side of the VBA editor, double-click on 'ThisWorkbook' to open its code window.
At the top of the code window, change the left dropdown menu from '(General)' to 'Workbook'. The right dropdown will automatically change to 'Open'. Click the right dropdown and select 'BeforeSave'.
Inside the Private Sub Workbook_BeforeSave routine, write code that uses WorksheetFunction.CountBlank to check your required ranges. If CountBlank returns a value greater than 0 for an active row, display a MsgBox warning and add the line 'Cancel = True' to abort the save.
Close the VBA editor. Go to File > Save As, change the 'Save as type' dropdown to 'Excel Macro-Enabled Workbook (*.xlsm)', and click Save.
Automate Data Entry Forms with WPS Spreadsheet
WPS Spreadsheet features an integrated, highly compatible Visual Basic Editor, allowing you to easily add custom BeforeSave macros to make required cells mandatory. It effortlessly handles Microsoft Excel macro formats.
- 1. Open your Document in WPS: Launch WPS Office and open your spreadsheet file.
- 2. Access the Developer Tools: Click on the 'Tools' tab on the ribbon, then click on 'Developer' to access the macro environment.
- 3. Open the Visual Basic Editor: Click 'Visual Basic Editor' (or press ALT + F11). Double-click 'ThisWorkbook' in the project window.
- 4. Add the Code and Save: Paste your BeforeSave macro code. Go to Menu > Save As and select the 'Microsoft Excel Macro-Enabled Workbook (*.xlsm)' format to ensure your script is saved.
Frequently Asked Questions
Can I use Data Validation instead of VBA to make cells mandatory?
Standard Data Validation restricts what users can type into a cell, but it cannot prevent a user from leaving a cell completely blank and saving the file. To completely block a file from saving due to empty required cells, VBA is necessary.
Why did my macro disappear after I saved and closed the file?
If you save your file as a standard Excel Workbook (.xlsx), all macros are automatically stripped out. You must choose 'Save As' and select 'Excel Macro-Enabled Workbook (*.xlsm)' to retain your VBA code.
How do I ensure entirely blank rows aren't flagged as incomplete?
In your VBA logic, you should first check if all cells in a specific row are empty. If the row is completely blank, instruct the macro to skip it and move to the next row, only triggering the 'Cancel = True' command if a row is partially filled.
