To restrict a range to dates with Excel VBA, use each cell’s Validation object and add a date rule with start and end limits. For example, the macro below allows dates from January 1, 2024, through December 31, 2026; change the range and limits to suit your sheet.
What date validation does
Excel data validation lets you define which entries are acceptable in selected cells. A date rule can require a date before, after, equal to, or between specified limits. You can also show an input message when a cell is selected and an error alert when someone enters a value that fails the rule.
In the Excel interface, the route on Windows is Data > Data Validation > Data Validation. In the dialog, choose Date under Allow, select a condition such as between, and enter the boundary dates. VBA automates those same settings, which is useful when a workbook has repeatable templates, many ranges, or rules that need to be set up consistently.
A validation rule is not the same thing as formatting. Formatting controls how a value appears; validation controls whether Excel accepts an entry during ordinary editing. A cell displaying a date-like pattern is not necessarily holding a valid date, and a validation rule does not guarantee that all data already in the cell is valid.
A VBA example for a fixed date range
This macro applies a between rule to B2:B100 on the worksheet named Entries. It permits blank cells, displays an explanatory input message, and stops ordinary entries outside the range. Adjust the sheet name, range, and date limits before running it.
Sub SetDateValidation()
Dim ws As Worksheet
Dim target As Range
' Select the worksheet and cells that need the rule.
Set ws = ThisWorkbook.Worksheets("Entries")
Set target = ws.Range("B2:B100")
' Remove any previous validation from this range.
target.Validation.Delete
' Allow dates from January 1, 2024 through December 31, 2026.
With target.Validation
.Add Type:=xlValidateDate, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=DATE(2024,1,1)", _
Formula2:="=DATE(2026,12,31)"
.IgnoreBlank = True
.ShowInput = True
.InputTitle = "Enter a date"
.InputMessage = "Use a date from Jan 1, 2024 through Dec 31, 2026."
.ShowError = True
.ErrorTitle = "Date outside range"
.ErrorMessage = "Enter a date within the permitted range."
End With
End Sub
Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. Save the file in a macro-enabled format, such as .xlsm, if you need to retain the code. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11; on a Mac, Option+F11 may work, or use the Developer tab’s Visual Basic command. Mac keyboards may require holding Fn as well.
How the VBA settings work
The .Add method creates the rule. xlValidateDate sets the validation type, while xlBetween says both Formula1 and Formula2 are limits. The strings beginning with =DATE are Excel formulas, so Excel evaluates them as dates in the workbook rather than treating a regional date string such as 1/2/2024 as potentially ambiguous.
xlValidAlertStop is the strictest standard alert style: Excel warns that the entry is invalid and does not accept it through the normal cell-entry process. A warning-style alert lets a person choose whether to continue, while an information-style alert is less restrictive. If the rule is meant to enforce a required range during manual entry, Stop is usually the appropriate choice.
.IgnoreBlank = True allows an empty cell, so this example does not make the date mandatory. If a date is required, set IgnoreBlank to False, but remember that this alone is not a complete guarantee that every cell will always contain a date. Users can clear a value, and other ways of changing workbook data may not follow the same validation prompt. Use a separate completeness check when missing dates matter.
Use worksheet cells for changing date limits
Fixed dates are straightforward, but they require editing the macro when the permitted period changes. A convenient alternative is to store the earliest and latest allowed dates in cells, for example F1 and G1 on the Entries sheet. Enter real Excel dates in those cells, then use those cell references as the limits:
Sub SetDateValidationFromCells()
Dim ws As Worksheet
Dim target As Range
' F1 and G1 hold the lower and upper date limits.
Set ws = ThisWorkbook.Worksheets("Entries")
Set target = ws.Range("B2:B100")
target.Validation.Delete
With target.Validation
.Add Type:=xlValidateDate, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=$F$1", _
Formula2:="=$G$1"
.IgnoreBlank = True
.ShowError = True
.ErrorTitle = "Invalid date"
.ErrorMessage = "Check the date limits in cells F1 and G1."
End With
End Sub
Those references are absolute, so the validation rule continues to refer to F1 and G1 when applied across the target range. Keep the limit cells on the same worksheet as the validated cells for the simplest setup, and make sure the lower limit is not later than the upper limit. If the limits are on another worksheet, use a defined name for the reference; data-validation formulas have restrictions on direct cross-sheet references. You can define names through Formulas > Name Manager in the Windows ribbon.
Applying the rule safely and adapting it
Before running either example, confirm that the worksheet name exists exactly as written and that the target range is the intended one. The call to Validation.Delete removes the old validation from those cells; it does not delete their values or formatting, but it will replace any previous validation settings. If existing rules must be preserved, inspect them first rather than running the example unchanged.
Useful adaptations include:
- Change
ws.Range("B2:B100")to the actual cells that accept dates. A single cell, such as B2, is also a valid target. - Use
xlGreaterorxlLessfor dates strictly after or before one boundary. UsexlGreaterEqualorxlLessEqualwhen the boundary date itself should be permitted. - Keep
.ShowInputand.ShowErrorenabled if people need clear guidance. Set custom message text that describes the required date range in plain language. - Set the cells’ number format separately if you want a consistent display, such as a four-digit year. Number formatting does not create or strengthen the validation rule.
For an inclusive range, xlBetween includes the boundary dates. If the cells might contain date-and-time values, consider that a date serial can include a fractional time. A limit of midnight on the final day may exclude later times on that same day. If users should enter only calendar dates, format the cells as dates and make the intended rule clear; if times are legitimate, set limits that account for them.
Common problems and misconceptions
A common issue is a runtime error when the macro names a worksheet that is not in ThisWorkbook. Check spelling, spaces, and which workbook contains the code. Another is a validation error caused by a missing or invalid boundary: when using worksheet limits, verify that F1 and G1 contain actual dates, not text that merely looks like a date, and that the start is no later than the end.
Regional settings can make typed dates confusing. For instance, 04/05/2025 can mean April 5 or May 4 depending on locale. The DATE function in the fixed-limit example avoids that ambiguity because its arguments are year, month, and day. When users type dates, give them a clear input message and use an unambiguous display format such as 5-Apr-2025 or 2025-04-05. Excel’s displayed format can still vary if users change formatting or regional settings.
Another misconception is that validation checks every possible way data enters a cell. It is designed mainly for interactive entry. Pasting, filling, importing, or changing values with another macro can bypass the usual warning or leave unexpected data in place. Depending on how content is copied, a paste can also replace validation settings. After importing data, inspect the results and reapply the rule if necessary. For stronger controls, use a separate audit formula, a review step, or controlled data-entry software rather than treating worksheet validation as a security boundary.
Finally, adding a rule does not retroactively certify the contents already in the selected range. Existing invalid values can remain. To review the range, use Data > Data Validation and the circle invalid data command in the validation menu, or check the cells with a separate formula or review process before relying on them.
Excel on Mac and Excel for the web
The validation concepts and VBA object model are broadly similar in Excel for Microsoft 365 on Windows and Mac, though ribbon arrangement, keyboard behavior, and the Visual Basic Editor can differ. On Mac, use the Data tab’s Data Validation command to inspect or set a rule manually; use the Developer tab’s Visual Basic command or the Mac shortcut to open VBA. If a keyboard shortcut does not work, the ribbon command is the reliable alternative.
Excel for the web can work with many existing validation rules, but it does not run VBA macros. Use desktop Excel to create or run these VBA examples. If a workbook must be edited in a browser without desktop macros, configure the rule with the web app’s data-validation controls where available, or use another supported automation approach such as Office Scripts; VBA code itself cannot be converted into a web-run macro simply by saving the workbook online.
Frequently Asked Questions
Can VBA restrict a cell to dates after today? Yes. Use a date validation rule with a formula based on =TODAY() as its lower limit, or use the VBA method with a suitable formula string. TODAY is recalculated as the current date, so the boundary moves over time. Test the rule in the workbook and consider whether the current date should be included or only later dates should be accepted.
How do I allow dates only within the current year? Use the first day of the current year as the lower limit and the last day as the upper limit. You can put those limits in worksheet cells and update them with formulas, or construct appropriate DATE formulas in VBA. Confirm that the range changes as expected when the calendar year changes, especially if the workbook will remain in use for several years.
Does Excel date validation prevent users from pasting invalid dates? Not reliably. Validation alerts are intended for ordinary cell entry and can be bypassed by some paste, import, or programmatic operations. Recheck imported or pasted values and use a separate audit if correctness is important.
How can I make the date field mandatory? Set .IgnoreBlank = False for the rule, but also check that the cell has a value before processing or submitting the record. Validation can be cleared or bypassed, so an independent completeness check is safer when missing dates would cause a problem.
Why does my date validation macro fail? Check that the worksheet exists in the workbook referenced by ThisWorkbook, that the target range is valid, and that the validation limits are valid dates in ascending order. Also confirm that the cells are not protected in a way that prevents changes, and that the macro is being run in desktop Excel rather than Excel for the web.
Related
- Convert Text to Numbers in Excel: Methods That Work
- How to Add the Copilot Icon in Excel
- Convert XLS to Excel: How to Save as an XLSX Workbook
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Can You Make Macros in Google Sheets? How They WorkYes. You can make macros in Google Sheets by recording a sequence of actions in the browser; Sheets…
- Excel Macro: Generate a Summary AutomaticallyYes. In Excel for Microsoft 365 on Windows, a VBA macro can read a data table, group rows…
- Word VBA: How to Add and Format Headers in a DocumentTo add a header with Word VBA, set the header range’s text, for example ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "Quarterly Report".…
- VBA Function Return Values: How to Return a ResultIn VBA, a function returns a value by assigning that value to the function’s own name; VBA does…
- Excel Macro Error? Common Causes and FixesAn Excel macro error usually means VBA is blocked, the code cannot find an expected workbook, worksheet, or…
- Excel VBA: Initialize Arrays, Set Values, and Resize ThemTo initialize an array in Excel VBA, declare it and assign values: use Dim items As Variant: items…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.