CODEWITHSUNDEEP
vbaexceldate
Vietson
Vietson

Reputation: 23

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

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!

Upvotes: 0

Views: 836

Answers (2)

Vietson
Vietson

Reputation: 23

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!

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 0

Related Questions