VBA & Macro Problems

Require Multiple Excel Cells Before Closing a Workbook

Steve KSteve K Nov 14, 2026 868 views

Question details

Require values in specified Excel ranges before allowing a workbook to close.

Require Multiple Excel Cells Before Closing a Workbook
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook must check cells N18:N34 and P4:P25 on the Business Unit worksheet when a user tries to close it.
Observed behavior
The desired behavior is to cancel closing and identify an incomplete cell until all required cells contain valid values.
Before you start

Confirm that the worksheet is named Business Unit and that every cell in N18:N34 and P4:P25 is required. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and use a desktop version of Excel that supports VBA.

Solution 1Recommended

Validate Required Ranges with Workbook_BeforeClose

Use a workbook event to cancel closing when a required cell is empty, contains only spaces, or has an error.

This example requires every cell in both ranges. It reports the first incomplete or error-containing cell; it does not implement a conditional rule between columns N and P.

1
Open the VBA editor

In desktop Excel, open the workbook and choose Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Open the workbook module

In the VBA editor's Project Explorer, expand your workbook and double-click ThisWorkbook. Add the event procedure there, not in a standard module.

3
Add the validation code

Paste this code into ThisWorkbook. If a Workbook_BeforeClose procedure already exists, incorporate the checks into that procedure instead of adding a duplicate: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet Dim cell As Range Dim isIncomplete As Boolean Set ws = ThisWorkbook.Worksheets("Business Unit") For Each cell In Application.Union(ws.Range("N18:N34"), ws.Range("P4:P25")) If IsError(cell.Value2) Then isIncomplete = True Else isIncomplete = (Len(Trim$(CStr(cell.Value2))) = 0) End If If isIncomplete Then Cancel = True MsgBox "Complete " & ws.Name & "!" & cell.Address(False, False) & " before closing.", vbExclamation Exit Sub End If Next cell End Sub

4
Save and test

Save the .xlsm file, reopen it with macros enabled, and try to close it while a required cell is empty. Enter a value in the cell named in the message and repeat until the workbook closes.

Validate Required Ranges with Workbook_BeforeClose
Macro limitation: The check runs only when VBA events and macros are enabled. It cannot enforce completion if macros are disabled or the application is forcibly terminated.
Free Microsoft Office alternative

Work with Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easier to move everyday spreadsheet work between suites. It does not fix Excel's VBA event behavior; check macro support before relying on this validation in another application.

Edit spreadsheets in a familiar interface.Open and edit common Microsoft Excel formats, including .xlsx files.Review required ranges such as N18:N34 and P4:P25 in a worksheet.Use a free, lightweight office suite for everyday document work.
microsoft office alternative - wps office

Frequently Asked Questions

Where should I put the BeforeClose code?

In the VBA editor, expand the workbook in Project Explorer and double-click ThisWorkbook. Place the Workbook_BeforeClose procedure in that module.

Can I require a cell in column P only when column N has a value?

Yes, but the code must use a conditional row-by-row rule instead of requiring both entire ranges. Define which rows correspond first; the example ranges overlap on rows 18 through 25.

Why does the workbook close without showing the message?

Check that the file is saved as .xlsm, macros are enabled when it opens, and the procedure is in ThisWorkbook. If events were disabled by another macro, run Application.EnableEvents = True in the VBA editor's Immediate window.

Does the validation save the workbook automatically?

No. The code only cancels closing when a required cell is incomplete. Once validation passes, Excel handles any normal save prompt when the workbook closes.