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
– Sam, Commented Jun 2, 2017 at 21:39
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
– Vietson, Commented Jun 2, 2017 at 21:42
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
– YowE3K, Commented Jun 2, 2017 at 22:06
you might fill in the wrong cell? Try it in immediate window (alt g), and with ´debug.print format(....)´ — Patrick Lepelletier
– Patrick Lepelletier, Commented Jun 3, 2017 at 0:59

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

0

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

answered Jun 3, 2017 at 15:11

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arya McCarthy Over a year ago

You should provide more detail to contextualize your code and explain how it answers the question. See How to Answer.

Dy.Lee Over a year ago

After formatting value, your values changed to text value(String). For reuse, you must change text value to datevalue by datevalue() functon.

Vietson · Accepted Answer · 2017-06-05 14:08:23Z

0

I figured out the problem. Cells C18 and C19 were formatted as dd-mmm. In order for the code to work properly, the year value must be included in the format. Changing to format to dd-mmm-yy or mmm dd, yyyy in cells C18 and C19 fixed the problem for me.

Thanks for the help!

answered Jun 5, 2017 at 14:08

Vietson

231 silver badge3 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related