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.
On this page are a few tips on how you can control the format.
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:
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")
Then it looks as my colleague wanted:
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 rInput = Worksheets(1).Range("A1:A7")
For Each rCell In rInput
Set rCell = Nothing
Set rInput = Nothing
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).
The same applies for a ListBox.