Userform ComboBox List Date Format

[ExcelHobbit]

I have a named list of dates called Jan_21, showing the following dates in separate cells: 04/01/2021, 11/01/2021, 18/01/2021, 25/01/2021

i then have a userform with a combobox with the list filling from Jan_21. However on the drop down, it shows the US format of mm/dd/yyyy to change it to 01/04/2021, 01/11/2021 etc. i have tried formatting the combobox to the correct date format of dd/mm/yyyy, but the lsit still appears incorrect and then pulls through an incorrect date.

how can i change this?

 

[GWteB]

You are most likely using the RowSource property of the combo box. I would advise against this, especially because of the problems you are currently experiencing.
Dates in Excel are actually numeric values (converted to an internal date type) while visual representations through controls are texts.
Depending on usage a value of the internal date type is converted back to a numeric value or vice versa. During these conversions, something unexpected can happen, depending on the regional setting.
It is therefore recommended to use the .AddItem method to populate your combo box.

Private Sub PopulateCombo()
    Dim c As Range
    With Me.ComboBox1
        .Clear
        For Each c In Range("Jan_21")
            .AddItem c.Text
        Next c
    End With
End Sub

 

[My Aswer Is This]

Try this:

Private Sub UserForm_Initialize()
'Modified  12/8/2021  2:45:59 PM  EST
ComboBox1.Clear
ComboBox1.List = Range("Jan_21").Value
End Sub

 

[ExcelHobbit]

You are most likely using the RowSource property of the combo box. I would advise against this, especially because of the problems you are currently experiencing.
Dates in Excel are actually numeric values (converted to an internal date type) while visual representations through controls are texts.
Depending on usage a value of the internal date type is converted back to a numeric value or vice versa. During these conversions, something unexpected can happen, depending on the regional setting.
It is therefore recommended to use the .AddItem method to populate your combo box.

Private Sub PopulateCombo()
    Dim c As Range
    With Me.ComboBox1
        .Clear
        For Each c In Range("Jan_21")
            .AddItem c.Text
        Next c
    End With
End Sub

this works perfectly, thank you so much. Not only correcting the date issue I had, but also reduced my code by 90%!

 

[GWteB]

You are welcome and thanks for the feedback