VBA & Macro Problems

Validate Required Excel Fields and Preserve Date Formats with VBA

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Validate required input cells before copying their values to another worksheet, while preserving date formats.

Validate Required Excel Fields and Preserve Date Formats with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA submission macro copies user-entered data from an input worksheet to a destination worksheet.
Observed behavior
Required fields need to be checked before submission, and copied dates can appear as serial numbers when their number formats are not preserved.
Before you start

Identify the required input cells, source range, and destination worksheet in your workbook. Save the file as an Excel Macro-Enabled Workbook (.xlsm) and enable macros only if you trust the workbook.

Solution 1Recommended

Validate Required Cells and Copy Values with Number Formats

Use this VBA approach to stop submission at an empty required field and preserve date formatting in the destination.

The example checks cells B2:B4 on a sheet named Input, then copies those cells to B2:B4 on a sheet named Records. Adjust the sheet names, required cells, and destination to match your workbook.

PasteSpecial with xlPasteValuesAndNumberFormats copies the displayed values and number formats, not the formulas or all other cell formatting.

1
Open the VBA editor

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

2
Add the submission macro

In the VBA editor, choose Insert > Module and paste this example: Sub SubmitData() Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim cell As Range Set sourceSheet = ThisWorkbook.Worksheets("Input") Set destinationSheet = ThisWorkbook.Worksheets("Records") For Each cell In sourceSheet.Range("B2:B4") If IsError(cell.Value2) Then MsgBox "Please correct the error in " & cell.Address(False, False) & "." sourceSheet.Activate cell.Select Exit Sub End If If Len(Trim$(CStr(cell.Value2))) = 0 Then MsgBox "Please complete required field " & cell.Address(False, False) & "." sourceSheet.Activate cell.Select Exit Sub End If Next cell sourceSheet.Range("B2:B4").Copy destinationSheet.Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats Application.CutCopyMode = False MsgBox "Data submitted." End Sub

3
Adapt and test the macro

Change the worksheet names and ranges in the module to match your form. Run the macro from Developer > Macros with a required field empty, then fill all fields and run it again to check the destination dates.

Validate Required Cells and Copy Values with Number Formats
Destination cells are overwritten: This example writes to Records!B2:B4 each time. Change the destination logic if submissions must be appended to a new row or stored elsewhere.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Open Excel files in WPS Spreadsheets for an easy transition, but use Excel to implement and test the VBA macro described above.

Open and edit spreadsheet data in a familiar interfaceCompatible with Microsoft Excel formats, including XLSXReview input fields and date formatting in WPS SpreadsheetsFree and lightweight for everyday office work
microsoft office alternative - wps office

Frequently Asked Questions

Why does a copied date appear as a number in Excel?

Excel stores dates as serial numbers. If the destination uses a General or numeric format, the serial number may appear instead of a date. Copy the source with xlPasteValuesAndNumberFormats or apply a date format to the destination cells.

How does the macro identify an empty required field?

The example loops through Input!B2:B4 and checks each cell with Len(Trim$(CStr(cell.Value2))). If the result is zero, it displays a message, selects that cell, and exits the procedure.

Will the macro copy formulas to the destination?

No. PasteSpecial with xlPasteValuesAndNumberFormats copies the formula results and number formats, not the formulas themselves.

How do I keep each submission instead of overwriting the previous one?

Replace the fixed Records!B2 destination with logic that finds the next available location on the Records sheet. Make sure the destination layout matches the shape of the source range before pasting.