VBA & Macro Problems

How to Require Complete Excel Table Rows Before Saving or Closing

Bushra ParveenBushra Parveen Nov 10, 2026 872 views

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.

How to Require Complete Excel Table Rows Before Saving or Closing
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

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

2
Insert the Event Code

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.

3
Create the Validation 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.

4
Update Table References

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.

5
Save as Macro-Enabled

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu.

Macro Security Limitations: If users open the file with macros disabled (e.g., due to the Mark of the Web from an internet download), this VBA enforcement will be completely bypassed. Users must click 'Enable Content' for this to work.
Efficient Spreadsheet Management

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. 1. Install WPS Office: Download and install WPS Office for free from the official website.
  2. 2. Open Your Workbook: Open your Macro-Enabled Workbook (.xlsm) using WPS Spreadsheets.
  3. 3. Enable Macros: Click 'Enable Macros' when prompted at the top of the screen to allow your data validation scripts to run.
  4. 4. Edit Code Seamlessly: Access the Developer tab to open the VBA Editor and tweak your table validation code effortlessly.
Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Built-in support for VBA macros to automate and enforce data entry rules seamlessly.Powerful conditional formatting rules to highlight incomplete table rows.Lightweight software with a familiar, easy-to-use interface.
microsoft office alternative - wps office

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.