Require Multiple Excel Cells Before Closing a Workbook
Question details
Require values in specified Excel ranges before allowing a workbook to close.

- 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.
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.
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.
In desktop Excel, open the workbook and choose Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.
In the VBA editor's Project Explorer, expand your workbook and double-click ThisWorkbook. Add the event procedure there, not in a standard module.
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
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.

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.

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.




