To display a date as dd/mm/yyyy in an Excel VBA UserForm TextBox, validate the text and format it in the TextBox’s Exit event; a TextBox does not have a built-in date number format. The example below accepts dates such as 3/4/2026, checks that the date is real, and changes it to 03/04/2026.
Why a UserForm TextBox needs VBA formatting
An Excel worksheet cell can store a date as a numeric value and use its Number Format setting to control how that value appears. A Microsoft Forms TextBox on a VBA UserForm is different: it mainly holds text. It does not provide a worksheet-style date format property that turns an entered value into a date and displays it as day/month/year.
That distinction matters because a string that looks like a date is not automatically a reliable date value. For example, 03/04/2026 might mean 3 April or March 4, depending on the date convention a person or computer assumes. A well-designed form should define the expected order, validate what the user entered, and only then format or save the date.
The code here explicitly treats the first number as the day, the second as the month, and the final four digits as the year. It does not ask Windows to guess what an ambiguous date means. This makes it appropriate when the form’s intended format is specifically day/month/year.
Add the TextBox event code
In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Find the UserForm in Project Explorer, double-click it, then double-click the date TextBox to create its Exit event. In this example the control is named txtDate; replace that name if your control has a different name.
Paste this event procedure into the UserForm’s code module. It allows a blank value, accepts one- or two-digit days and months with slash separators, requires a four-digit year, rejects impossible dates, and formats valid input when the user leaves the box.
Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim raw As String
Dim parts As Variant
Dim dayNum As Integer
Dim monthNum As Integer
Dim yearNum As Integer
Dim parsedDate As Date
' Allow the field to be blank; remove this if a date is required.
raw = Trim$(Me.txtDate.Value)
If Len(raw) = 0 Then Exit Sub
parts = Split(raw, "/")
If UBound(parts) <> 2 Then GoTo InvalidDate
' Require numeric day/month parts and a four-digit year.
If Not (parts(0) Like "#" Or parts(0) Like "##") Then GoTo InvalidDate
If Not (parts(1) Like "#" Or parts(1) Like "##") Then GoTo InvalidDate
If Not (parts(2) Like "####") Then GoTo InvalidDate
dayNum = CInt(parts(0))
monthNum = CInt(parts(1))
yearNum = CInt(parts(2))
' Keep values in range before asking DateSerial to build a date.
If dayNum < 1 Or dayNum > 31 Then GoTo InvalidDate
If monthNum < 1 Or monthNum > 12 Then GoTo InvalidDate
If yearNum < 1000 Or yearNum > 9999 Then GoTo InvalidDate
parsedDate = DateSerial(yearNum, monthNum, dayNum)
' DateSerial rolls excess days into the next month, so verify the parts.
If Year(parsedDate) <> yearNum Then GoTo InvalidDate
If Month(parsedDate) <> monthNum Then GoTo InvalidDate
If Day(parsedDate) <> dayNum Then GoTo InvalidDate
' Backslashes make the slashes literal separators in the displayed format.
Me.txtDate.Value = Format$(parsedDate, "dd\\/mm\\/yyyy")
Exit Sub
InvalidDate:
MsgBox "Enter a valid date as dd/mm/yyyy, for example 03/04/2026.", _
vbExclamation, "Invalid date"
Cancel = True
End Sub
The event procedure’s declared variables hold the entered string, its three separated parts, and the numeric date components. The Cancel argument is important: setting it to True prevents the user from leaving the TextBox after invalid input, so they can correct the entry. If a date is mandatory, remove the blank-value early exit and handle an empty string as invalid instead.
How the validation avoids ambiguous dates
The procedure uses Split to separate the text at slash characters, then reads the components in a fixed order. For 3/4/2026, it assigns 3 to the day, 4 to the month, and 2026 to the year. The formatted result is 03/04/2026. Someone who means March 4 must enter 4/3/2026 under this day-first rule.
Using DateValue on the original text may seem shorter, but it interprets date strings according to regional settings. That can make the same text mean different dates on different computers. Parsing the parts and using DateSerial(year, month, day) makes the intended order explicit.
There is another subtle issue: DateSerial normalizes out-of-range days. For example, a day that exceeds the number of days in a month can roll into the following month instead of simply failing. The comparisons against Year, Month, and Day catch this behavior. Thus 31/04/2026 is rejected rather than silently becoming a date in May, and 29/02/2025 is rejected because 2025 is not a leap year.
When the formatting happens
The Exit event runs when focus moves away from the TextBox, such as when the user clicks another control or tabs to the next one. That timing lets people enter a date naturally and see the padded two-digit day and month after leaving the field. The procedure does not reformat after every keystroke, which could disrupt typing by moving the cursor or inserting separators before the user has finished.
If you want validation to happen at a different moment, choose an event that matches the form’s behavior. An AfterUpdate event runs after the value changes and is accepted; a command button’s Click event can validate all required fields when the user submits the form. Regardless of the event, keep the explicit parsing and validity checks if day-first interpretation is required.
Do not rely on a TextBox’s ControlSource or a worksheet cell’s number format to validate what a person typed. A cell might display a stored date with a chosen format, but that does not guarantee that the original form input was interpreted as intended. Validate at the form boundary, then write a genuine date value to the worksheet if later calculations or sorting require one.
Showing the date and storing a real date value
Formatting the TextBox gives the user a consistent display, but the control’s value is still text. If the form saves the date to a worksheet, store a VBA Date value rather than treating the formatted string as a date formula or relying on Excel to infer its meaning. In the sample, parsedDate is already a validated date value and is available for the rest of the event procedure.
For example, after validation you could assign that value to a destination cell in your own save routine, then set that cell’s display format to dd/mm/yyyy. The cell would retain a date serial value for sorting and date arithmetic while displaying the requested order. Set the worksheet cell’s NumberFormat in VBA if needed; that property applies to the cell, not to the UserForm TextBox. Avoid saving only the text if another part of the workbook will compare dates, calculate intervals, or sort records chronologically.
Keep data validation and saving separate when the form contains several fields. First confirm every required entry is valid; then write the record. Otherwise, a partially completed record may be written before a later field fails validation. Also consider how a blank date should be represented in the sheet: an empty cell is usually clearer than writing a zero date or a text string that looks blank.
Regional settings and the slash character
The code’s date order is fixed as day, month, year, but regional settings can still affect how separators and dates are displayed elsewhere. In VBA date format strings, a slash can act as a locale-sensitive date separator. The escaped slash in Format$(parsedDate, "dd\/mm\/yyyy") requests literal slash characters in this TextBox display, so the intended output remains like 03/04/2026.
This matters when a workbook moves between computers. A user may have a system convention that normally displays dates with a different separator or in month-first order. The explicit parsing in the example keeps the meaning day-first; the literal separators keep the TextBox display consistent. When writing the validated value to a worksheet, set that cell’s number format deliberately as well, and check how the workbook appears in the target locale.
Common errors and practical improvements
- The TextBox is named differently: change every occurrence of
txtDateto the control’s actual (Name) property. The visible caption or nearby label is not necessarily the control name. - Code is placed in the wrong module: an event procedure belongs in the UserForm module associated with that TextBox, not in a standard module or a worksheet module.
- The code rejects a date you expect it to accept: this example requires slash separators and a four-digit year. It does not accept hyphens, month names, or two-digit years. Add and test those formats deliberately rather than passing them to locale-dependent interpretation.
- The form reopens with an old value: initialize the TextBox or load it from the selected record when the form opens. Formatting on exit only runs when the user leaves the field; it does not automatically format a value assigned during form initialization.
- Users need a different date convention: make the expected order visible in a label or example beside the box. A clear prompt such as “Date (dd/mm/yyyy)” reduces accidental month/day reversals.
You can also make the field required, provide a calendar control where appropriate, or validate when the user clicks Save. A calendar can reduce typing errors, but it does not remove the need to confirm that the selected date is the intended one. Keep the accepted input rules and error message consistent so the user knows how to fix a rejected entry.
Excel platform and macro considerations
This approach is for a VBA UserForm in the desktop Excel application. Save the workbook in a macro-enabled format such as .xlsm if it contains VBA, and enable macros only for workbooks you trust. Before running or changing a macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z.
Excel for the web does not run VBA macros or display VBA UserForms, so a form built this way is not a web-based data-entry solution. Excel for Mac has VBA support, but UserForm and control behavior can differ from Windows; check the form and its controls in the Mac version your users will use. The Windows Visual Basic Editor shortcut shown above is Alt+F11; on Mac, the editor shortcut and access can vary with keyboard settings, and the Visual Basic Editor can be opened from Excel’s Developer tools.
Frequently Asked Questions
Can I set a UserForm TextBox number format to dd/mm/yyyy?
No. A standard MSForms TextBox does not have the same number-format setting as a worksheet cell. Use VBA to validate the entered text and format the displayed value, as in the event procedure above.
Why does DateValue change the meaning of my date?DateValue interprets text using regional date conventions. An entry such as 03/04/2026 is ambiguous unless the day/month order is specified. Parse its components explicitly when the form must always mean day/month/year.
Can the TextBox show a leading zero for the day and month?
Yes. The format mask uses two-digit day and month tokens, so entries such as 3/4/2026 display as 03/04/2026 after the user leaves the field.
Will the sample accept 31/04/2026?
No. Although DateSerial can roll an excessive day into the next month, the code compares the resulting date’s parts with the entered parts and rejects the mismatch.
Does this code make the TextBox value a true Excel date?
No. The TextBox displays text. The code separately holds a validated VBA Date in parsedDate; use that value when saving to a worksheet cell if you need date calculations or chronological sorting.
Will this work in Excel for the web?
No. Excel for the web does not run VBA UserForms. Use a desktop Excel workbook for this macro-based form, or choose a different data-entry method for browser users.
Related
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
- Power Query Split Cell Into Rows: Steps and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox Character Limit and Longer Text OptionsThe standard VBA InputBox is limited to about 255 characters of entered text, so it is not suitable…
- Google Sheets Macros: How They Work and What to KnowYes. Google Sheets supports macros: you can record repeated actions and run them again, or edit the generated…
- VBA AutoFilter Date: Filter Dates ReliablyTo filter dates with VBA, apply AutoFilter to a range that includes its header row, then set date…
- VBA Sort Worksheets Alphabetically: Code and SetupYes. In Excel for Microsoft 365 on Windows, VBA can sort a workbook’s worksheet tabs alphabetically; the macro…
- VBA Access Find Record: FindFirst, FindRecord, and MoreTo find and move to a matching record in an Access form, use its RecordsetClone with FindFirst, check…
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.