How to Require Complete Excel Table Rows Before Saving or Closing
Question details
The user wants to ensure that all required cells in a specific Excel table row are filled out before allowing the workbook to be saved or closed.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Users are entering records into an Excel table, and data integrity requires entire rows to be completed without any blank cells before the file is stored.
- Observed behavior
- Currently, users can save or close the workbook leaving incomplete rows, resulting in missing data and incomplete records.
Before applying VBA code, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that users are instructed to enable macros when opening the file, otherwise the rules will be bypassed.
Use VBA Workbook Events to Prevent Saving and Closing
Implement VBA code in the Workbook_BeforeSave and Workbook_BeforeClose events to check if table rows are complete.
This method uses a custom VBA function to loop through a specified table. If any cell in a row is empty, the code cancels the save or close action and alerts the user with a message box.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, double-click on 'ThisWorkbook'. Paste your Workbook_BeforeSave and Workbook_BeforeClose event handlers here, which call your custom IsTableComplete function.
Write a Function IsTableComplete() that sets your target Worksheet and ListObject (table), then loops through each ListRow. If IsEmpty(cell.Value) triggers, set the function to False and exit.
Make sure to update the worksheet name (e.g., 'Sheet2') and table name (e.g., 'Table14') in your VBA script to match the exact names used in your workbook.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu.
Highlight Incomplete Rows Using Conditional Formatting
A softer, non-VBA approach to guide users by visually highlighting required cells that are left blank.
Manage Excel Tables and Macros Seamlessly with WPS Office
WPS Office perfectly supports VBA macros, conditional formatting, and advanced table management, allowing you to enforce data entry rules easily without needing an expensive Microsoft Office subscription.
- 1. Install WPS Office: Download and install WPS Office for free from the official website.
- 2. Open Your Workbook: Open your Macro-Enabled Workbook (.xlsm) using WPS Spreadsheets.
- 3. Enable Macros: Click 'Enable Macros' when prompted at the top of the screen to allow your data validation scripts to run.
- 4. Edit Code Seamlessly: Access the Developer tab to open the VBA Editor and tweak your table validation code effortlessly.

Frequently Asked Questions
Why is my VBA code not running when others open the file?
By default, spreadsheet applications disable macros downloaded from the internet or network drives for security reasons (known as Mark of the Web). Users must explicitly click 'Enable Content' or unblock the file in Windows properties for the VBA enforcement to activate.
Can I apply this VBA check to a specific column instead of the whole row?
Yes, you can modify the IsTableComplete function in your VBA code to loop through a specific ListColumn range within the table instead of checking every cell across the entire ListRow.
Is there a way to absolutely force users to enable macros?
There is no built-in 'force enable' feature due to security constraints. A common workaround is to hide all functional worksheets and show only a warning sheet that says 'Please Enable Macros'. Once macros are enabled, a Workbook_Open VBA event runs to unhide the actual data sheets.




