Date format in an Excel VBA ComboBox or ListBox

Having dates in a ComboBox or ListBox in Excel VBA can be a real teaser, because VBA changes the date format, and that may not be the way you want the dates displayed.

A colleague had made a userform in VBA, where a ComboBox read some dates from the worksheet, but the dates were formatted differently in the ComboBox, and he wanted to change that.

Let us say that cell A1 had the formula "= TODAY()", in cell A2 it was "= A1 + 1" etc. - a total of 7 dates. He had formatted the cells, so that the dates were displayed as dd-mm-yyyy, e.g. "28-07-2012".

In the userform's Initialize procedure he read the cell values (dates) into the ComboBox with:

With ComboBox1
   .AddItem Range("A1").Value
   .AddItem Range("A2").Value
   .AddItem Range("A3").Value
   .AddItem Range("A4").Value
   .AddItem Range("A5").Value
   .AddItem Range("A6").Value
   .AddItem Range("A7").Value
End With

It worked fine, but it annoyed him that the dates were shown in the format: "7/28/2012" instead of "28-07-2012" like the cells:

I suggested that he could use the ComboBox' RowSource property instead. I marked his .AddItem-kode as a comment and wrote "A1:A7" in the ComboBox' RowSource property. (RowSource can also be set runtime in the Initialize-proceduren), and now it looked like this:

That was exactly what he wanted BUT ... Now, when you clicked on a date, the selected date was shown as a number in the ComboBox' text box. Like this:

Now, you can solve that problem by inserting the following procedure in the userform:

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "dd-mm-yyyy")
End Sub

At the time I didn't think of that solution, so instead we fixed the problem with the following code in the userform's Initialize-procedure (after having deleted A1:A7 in the RowSource property):

Private Sub UserForm_Initialize()
Dim rCell As Range
Dim rInput As Range

'Set the range rInput = the range A1:A7
Set rInput = Worksheets(1).Range("A1:A7")

'We now loop the range, and the trick is to convert the date to text (String).
For Each rCell In rInput
   ComboBox1.AddItem Str(rCell.Value)
Next

Set rCell = Nothing
Set rInput = Nothing
End Sub

It worked as desired, and my colleague was happy. The trick was to convert the dates to text of the data type String using the function "Str()", because a String will be displayed exactly like it is in the cell you read from.

If you need to work with the selected date as a real date (Date data type), you can convert it back with the VBA function CDate(date as String).