Why does work Cdate() only with english months?

Question

I have a drop down box, the items are depending on the regional settings:

Private Sub UserForm_Initialize()
Select Case Application.International(XlApplicationInternational.xlCountryCode)
   Case 1: 'English
   With ComboBox1
        .AddItem "January"
        ...etc
   End With
   Case 36: 'Hungarian
   With ComboBox1
        .AddItem "Január"
        ...etc
   End with
   Case 49: 'German
        With ComboBox1
           .AddItem "Januar"
           ...etc
   End with
End Select
End Sub

Later I use the selected value in this code:

Year_1 = 2017 'integer
Day_1 = 1 'integer
Date_from_userform = CDate(Year_1 & "-" & UserForm1.ComboBox1.Value & "-" & Day_1) 'date

In German environment, it works perfect, but I tested in Hungarian environment, and I get every time type mismatch.

The Cdate does not accept the 2017-Január-1. (Th excel was Hungarian)Why?

If the month depends on the regional settings, it should work... (Or should I convert the Values from the dropdownbox into numbers?)

`CDate` respects the Windows regional settings, not Excel language. — GSerg, Mar 07 '17 at 21:33
In any case you should not be parsing localized month names. Create a [multicolumn list](http://stackoverflow.com/q/6973287/11683) in the dropdown and use separate columns for caption (localized month name) and value (month number). — GSerg, Mar 08 '17 at 08:12

score 0 · Answer 1 · answered Mar 07 '17 at 19:33

Instead of writing all the months yourself (and possible risk a misspelling) I'd use the following approach to write the months "names" for you:

Public Sub getMonthNamesWrittenOutInDifferentLanguages()

Dim i As Integer

'Hungarian:
For i = 1 To 12
    Debug.Print Application.WorksheetFunction.Text(DateSerial(2017, i, 1), "[$-40e]MMMM")
Next i

'English:
For i = 1 To 12
    Debug.Print Application.WorksheetFunction.Text(DateSerial(2017, i, 1), "[$-809]MMMM")
Next i

'German:
For i = 1 To 12
    Debug.Print Application.WorksheetFunction.Text(DateSerial(2017, i, 1), "[$-de-DE]MMMM")
Next i

End Sub

Afterwards, you can use the same code to easily go through all the possible month names and convert them back to dates:

strDate = "2017-Januar-05"

For i = 1 To 12
    strDate = Replace(strDate, Application.WorksheetFunction.Text(DateSerial(2017, i, 1), "[$-de-DE]MMMM"), i)
Next i

Debug.Print IsDate(strDate)
Debug.Print CDate(strDate)

Hey, thanks! :) I wish i could give both of you a green tick. — randomname, Mar 08 '17 at 08:29

score 0 · Accepted Answer · answered Mar 07 '17 at 20:28

I would use the Format function, the DateSerial function and the ComboBox1.ListIndex property.

Private Sub CommandButton1_Click()
 Year_1 = 2017 'integer
 Day_1 = 1 'integer
 Date_from_userform = DateSerial(Year_1, ComboBox1.ListIndex + 1, Day_1)
End Sub

Private Sub UserForm_Initialize()
 For i = 1 To 12
  ComboBox1.AddItem Format(DateSerial(Year(Date), i, 1), "mmmm")
 Next
End Sub

Why does work Cdate() only with english months?

2 Answers2