Why Excel Data Validation Does Not Trigger for VBA Date Entries
Question details
The user needs to prevent a VBA UserForm or date picker from writing dates outside an allowed range to an Excel cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A date picker writes a selected date to a worksheet cell that has a data validation rule.
- Observed behavior
- An invalid date entered manually triggers validation, but an invalid date written by the date picker does not.
Identify the UserForm control, destination cell, and code that writes the selected date. Check the permitted start and end dates in the cell's Data > Data Validation settings or in the variables used by your form.
Validate the date in VBA before writing it to the worksheet
Use this approach when a UserForm, date picker, or macro writes directly to a cell.
Excel's data validation alert is designed for direct user entry and may not run when VBA assigns a value. Check the date in the procedure that writes to the cell rather than relying on the worksheet alert.
Open the VBA editor with Alt+F11, find the UserForm or module in Project Explorer, and open the procedure that assigns the date to the worksheet cell.
Before the cell assignment, test that the selected value is a date and falls within your allowed bounds. For example, adapt this code inside the procedure: Dim selectedDate As Date If Not IsDate(Me.txtDate.Value) Then MsgBox "Enter a valid date." Exit Sub End If selectedDate = DateValue(CDate(Me.txtDate.Value)) If selectedDate < DateValue(YrStartDate) Or selectedDate > DateValue(YrEndDate) Then MsgBox "Choose a date within the allowed range." Exit Sub End If Worksheets("Sheet1").Range("A1").Value = selectedDate
Replace Me.txtDate, Sheet1, and A1 with your control, sheet, and cell names. Ensure YrStartDate and YrEndDate contain valid boundary dates, then run the form and test dates before, within, and after the permitted range.

Restrict the dates available in the UserForm
Use this approach to prevent users from selecting an out-of-range date in the first place.
Check validation after copying or pasting cells
Use this when pasted values may have bypassed or replaced the destination cell's validation rule.
Work with Excel date-validation sheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface. Open compatible Excel workbooks in WPS Spreadsheets for a straightforward transition, while using Excel to maintain or troubleshoot Excel-specific VBA behavior.

Frequently Asked Questions
Why does a manual entry show an error but a VBA entry does not?
Excel's data validation alert is intended for direct entry and may not run when a macro assigns a cell value. Check the value in the VBA procedure before assigning it to the cell.
Can a date picker prevent users from choosing invalid dates?
Yes, if the control supports minimum and maximum date properties, set them when the UserForm opens. Otherwise, add range checks to the form's selection or submission code.
Can pasting remove an Excel data validation rule?
Yes. Some paste operations can replace the destination cell's validation rule. Select the cell and open Data > Data Validation to inspect and restore the rule if needed.
How can I find dates that already violate a validation rule?
On Excel's Data tab, open the Data Validation menu and select Circle Invalid Data. This checks cells against their existing validation rules, so restore any missing rules first.




