Format dates in a combobox

[Jeff H]

For an Excel form that deals with invoices, I populate a 2 column combobox with the following code, which works fine:

Code: Select all

Set rng1 = ActiveSheet.Range("colDates")
iRows = rng1.Rows.Count
Set rng2 = rng1.Offset(1, 0).Resize(iRows - 2, 2)
AllDates = rng2.Value

Me.cmbDates.List = AllDates

However, these invoices cover the month prior to the billing dates in colDates. In the spreadsheet, the Month of Service column to the right of colDates uses the formula =EDATE(A8,-1) and is formatted as text like this: “mmm yyyy”.

The code as written, of course, enters the second column in normal date format ("m/d/yyyy"). I’m wondering if there is a way I can format column 2 of the combobox to display the billing period as text.

In fact, I'd be happy to use a 1-column combo if I could format all the dates as "mmm yyyy".

[HansV]

Change the formula to

=TEXT(EDATE(A8,-1),"mmm yyyy")

and fill down.

[Jeff H]

Doh!! Of course. Thanks Hans. Sorry for coming out here without having thought it through enough.