Excel VBA - Unable to change year in date format in TextBox in Userform

Question

I have a userform with two TextBox fields for dates. When I fill in the textboxes and press "tab" or click on the other box, the field will first fill in a cell on a worksheet and then format the date in the textbox (This allows for date entry in different formats such as 1/1/17, no matter what it should return as mmm dd, yyyy).

Here's my issue. Everything works absolutely perfectly, but the year is always stuck on 2017. I tried entering the date in all formats, the month and date change accordingly, but the year remains on 2017.

Here is the code below:

Private Sub Button_OK_Click()

Sheets("Inputs").Range("C18").Value = Format(TextBox_StartDate.Value, "mmm dd, yyyy")
TextBox_StartDate.Value = Format(Sheets("Inputs").Range("C18").Text, "mmm dd, yyyy")
Sheets("Inputs").Range("C19").Value = Format(TextBox_EndDate.Value, "mmm dd, yyyy")
TextBox_EndDate.Value = Format(Sheets("Inputs").Range("C19").Text, "mmm dd, yyyy")
If TextBox_StartDate.Value = "" Or TextBox_EndDate.Value = "" Then
    MsgBox ("Start and End Dates must be filled in to continue.")
ElseIf Sheets("Inputs").Range("C18").Value > Sheets("Inputs").Range("C19").Value Then
    MsgBox ("End Date must be after Start Date.")
ElseIf Not (IsNumeric(Sheets("Inputs").Range("C18").Value) And IsNumeric(Sheets("Inputs").Range("C18").Value)) Then
    MsgBox ("Date fields must be filled in properly.")
Else
    Continue = True
    Unload Me
End If
End Sub

Private Sub TextBox_StartDate_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 9 Then
    Sheets("Inputs").Range("C18").Value = Format(TextBox_StartDate.Value, "mmm dd, yyyy")
     TextBox_StartDate.Value = Format(Sheets("Inputs").Range("C18").Text, "mmm dd, yyyy")
    Sheets("Inputs").Range("C19").Value = Format(TextBox_EndDate.Value, "mmm dd, yyyy")
     TextBox_EndDate.Value = Format(Sheets("Inputs").Range("C19").Text, "mmm dd, yyyy")
End If
End Sub

Any help would be much appreciated!

I copied your code and I don't get your error. It behaves as I think you'd like it. I run Excel 2016 with Swedish locale. — Sam
I don't get an error. My issue is that when the date autofills in the textbox, it always fills with the year as 2017 no matter what other year I put in. — Vietson
Your code works for me. E.g. If I enter "5/6/15" into TextBox_StartDate it correctly converts it to "Jun 05, 2015". I don't like the way you are doing it (I would prefer to treat dates as dates, and not convert them to text, and then back to a date, in order to then convert them to text again), but it seems to work. — YowE3K
you might fill in the wrong cell? Try it in immediate window (alt g), and with ´debug.print format(....)´ — Patrick Lepelletier

Dy.Lee Dy.Lee · Accepted Answer · 2017-06-03T15:11:28

Sheets("Inputs").Range("C18").Value = Format(DateValue(TextBox_StartDate.Value), "mmm dd, yyyy")

Excel VBA - Unable to change year in date format in TextBox in Userform

2 Answers