[Webinar] Streamline your web hosting managementRegister Today


VBA - Passing Array of dates to Combobox / Listbox

Posted on 2010-04-10
Medium Priority
Last Modified: 2012-06-27
Hi there,

I have a list of dates in a range on a worksheet that I use to populate a combobox.  I have formatted the dates as "dd-mmm-yy".  When it gets passed to the control, it loses the formatting I prefer and instead formats as "dd/mm/yyyy".  

I hope I am being stupid and there is an easy property somewhere that allows me to switch such that it sticks with the format I prefer, but regardless, does anyone have any suggestions as to how to pass the dates to the listbox (other than one at a time as a string), to avoid the change in formatting.

Just for reference the code I am using is as follows:

varImport = Application.WorksheetFunction.Transpose(wksMain.Range("Import_Qtrs").Value)
frmSelectQtrs.cmbQtr1.List = varImport

Many thanks,

Question by:Digittt

Expert Comment

ID: 30303623
it should be possible to set:
ComboBox1 = Format(ComboBox1, "dd-mm-yy")

or there is a propery called
.NumberFormat = "dd-mm-yy"

LVL 16

Accepted Solution

RichardSchollar earned 2000 total points
ID: 30304560

You could use Evaluate to generate a 2D array in the correct format like this:

However, it would quite possibly be faster (not that you're likely to notice in the real world) to assign the values via a 2D array and then add them one at a time via the Add method of the combo.


varImport = Application.WorksheetFunction.Transpose(Evaluate("IF(ROW(),TEXT('" & wksMain.Name & "'!" & wksMain.Range("Import_Qtrs").Address & ",""dd-mmm-yy""))"))

'and then assign this:

frmSelectQtrs.cmbQtr1.List = varImport

Open in new window


Expert Comment

ID: 30304681
I couldn't find a flag to set, but this works.
varImport = Application.WorksheetFunction.Transpose(wksMain.Range("Import_Qtrs").Value)
For Item = 1 To UBound(varImport)
    varImport(Item) = Format(varImport(Item), "dd-mm-yy")
Next Item
frmSelectQtrs.cmbQtr1.List = varImport

Open in new window


Author Comment

ID: 30328868
Evaluate worked, although I have no idea why...haven't used it before....hmmm, a new toy to play with.

I tried the Format / .NumberFormat options, they were a no-go, but thanks for the idea.  

On the For Next, I didn't want to iterate through it individually but it would work.  No one would notice given the size of the array but I was looking for something more contained that didn't have to do it one at a time.

Thanks all for the suggestions.


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

613 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question