VBA & Macro Problems

How to Validate Required Excel Fields and Reference Numbers with VBA

Algirdas Jasaitis Sep 30, 2026 869 views

Question details

The user needs to enforce mandatory fields, strict reference number formats (e.g., CUSN00-000), and conditional validation for an online order receipt number using VBA.

How to Validate Required Excel Fields and Reference Numbers with VBA

Product: Excel
Device & OS: not provided
Scenario: Creating an Excel customer data entry form or table that requires strict data validation before allowing the user to save the document.
Observed behavior: Without validation, users can leave required fields blank, enter improperly formatted customer reference numbers, or skip conditional data such as receipt numbers.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet program and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA code execution.

Solution 1Recommended

Implement a Validate and Save VBA Macro

Create a macro attached to a save button that checks all mandatory fields, reference number formats, and conditional logic before allowing the user to save the data.

This approach ensures data integrity by preventing incomplete or incorrectly formatted rows from being saved to the database or final worksheet.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic Editor. Go to Insert > Module to create a new standard module, and define a new sub-routine named ValidateAndSave.

Check for Blank Mandatory Fields

Write a loop to check the designated columns (e.g., Columns B through E) for blank values. Use an If statement to prompt a MsgBox error identifying the affected row if empty cells are found.

Validate the Customer Reference Number Format

Use the Like operator in VBA with the pattern "CUSN##-###" to validate the customer reference number in Column A. If the entry does not match this pattern, trigger an alert and exit the sub-routine.

Apply Conditional Logic for Receipt Numbers

Add an If statement to check if the online purchase field equals 'Yes'. If true, verify that the corresponding receipt number field is not empty. If 'No' is selected, bypass this check.

Assign Macro to a Button

Return to your worksheet, insert a Form Control button from the Developer tab, name it 'Save Data', and assign your ValidateAndSave macro to this button.

Tip: Instead of using a button, you can also place this validation code inside the Workbook_BeforeSave event in the 'ThisWorkbook' module to trigger the checks automatically when the user attempts to save the file.

Solution 2

Apply Real-Time Validation using a Worksheet_Change Event

Trigger a validation script immediately when a user finishes typing a customer reference number or online purchase selection.

Advanced Spreadsheet Features

Validate Data Efficiently with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA macros, enabling you to build powerful data validation rules, format checking, and conditional logic seamlessly within your workbooks.

1. Open your file in WPS Spreadsheet: Download and launch WPS Office, then open your macro-enabled spreadsheet.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and select Visual Basic to open the coding environment.
3. Insert Validation Macros: Insert a new module and write your ValidateAndSave or Worksheet_Change macros just as you would in standard VBA environments.
4. Save and Execute: Assign the macro to a button or sheet event to apply real-time conditional validation and save your file.

Full compatibility with Microsoft Excel macro-enabled (.xlsm) formatsRobust built-in VBA editor for writing and testing validation macrosLightweight and fast execution for heavy data processing scriptsAdvanced data validation features for enforcing strict input rules

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Excel's built-in Data Validation instead of VBA for this scenario?

While built-in Data Validation can handle basic lists and simple formulas, enforcing complex conditional dependencies (like requiring a receipt only if 'Yes' is selected) and exact structural pattern matching (CUSN##-###) are handled much more strictly and securely using a VBA macro.

Why is my Worksheet_Change event causing the spreadsheet to freeze?

If your Worksheet_Change code alters a cell's value (such as clearing an invalid reference number), it triggers the event again, creating an infinite loop. To prevent this, wrap your cell-modifying code between 'Application.EnableEvents = False' and 'Application.EnableEvents = True'.

How do I ensure users do not bypass the macro validation by just saving the file?

You can place your validation logic inside the Workbook_BeforeSave event located in the 'ThisWorkbook' module. If the validation fails, you can set 'Cancel = True' within the code to completely stop the saving process until the user fixes the errors.

Excel & Data Problems