Format Combo Box To Date Format

[Makpo]

Does Anyone know how to format a combo box to display as dates rather in a general format.

I have some dates on a table linked to a combo box drop down. When selected on the drop down, it keeps presenting in a general number format.

please help

 

[mikerickson]

Dim i As Long

With ComboBox1
    For i = 0 To .ListCount - 1
        .List(i) = Format(.List(i), "mm/dd/yyyy")
    Next i
End With

 

[CWatts]

Alternatively, you could use a Forms Control combo box instead of ActiveX depending on your need.

 

[Makpo]

Hi Mikerickson,

I tried it but it didnt work. my combo box is on a userform. am I to use the code within the command button click or on the combo box click itself?

Assuming its a subroutine on its own?

 

[Makpo]

I tried the code below and it displayed date format on the combo box correctly but entered a wrong month and year on the data (it seems to be a default date year of 1900) but the day was correctly entered.

Private Sub cboeDate_Change()
    
    With cboeDate
        .value = Format(.value, "dd/mm/yyyy")
    End With
      
End Sub

 

[mikerickson]

That may be a result of the dd/mm/yyyy format (rather than mm/dd/yyyy)

 

[Makpo]

Thanks, I already changed it from mm/dd/yyyy to dd/mm/yyyy but it still didnt work.

I resorted to hiding the visibility of the numbers on the combo box and settled for just the drop down arrow to show the dates without the code, since it copies correctly to my desired location.

not the best but I'll make do. Thanks again

 

[CWatts]

I think this is what you were looking for on the combobox. Right click on your Userform in your VBA project explorer and choose View Code and pop this in:

Private Sub ComboBox1_Click()
ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yyyy")
End Sub

 

[Makpo]

Thanks CWatts,

But Not exactly, it displays the value of the date as a date, only to populate the target cell with the month and year wrongly to a default of /01/1900. The day is however pasted correctly

 

[CWatts]

Hrm. The only thing I can think of is that it's somehow passing text, which has a numerical value of 0, which would be a d/m/yyyy of 0/1/1900

Try using datevalue:

Private Sub ComboBox1_Click()
ComboBox1.Value = Format(DateValue(ComboBox1.Value), "dd/mm/yyyy")End Sub