Combo list to format date dd/mm/yyyy

inkbird1

Board Regular
Joined
Apr 21, 2020
Messages
51
Hi everyone.

I have a combo box pulling a range of dates however the date in the combo box is showing mm/dd/yyyy when the cell range is dd/mm/yyyy
Wondering if i need a format code in the following line? thanks!

VBA Code:
cboDateStart.List = Range("Lists!A2:A32").Value
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
VBA uses US date format i.e month-day-year.
Try using Loop & Text property, something like this:
VBA Code:
ComboBox1.Clear
    For Each r In Sheets("Sheet1").Range("A1:A5")
        ComboBox1.AddItem r.Text
    Next
 
Upvote 0
Try using this line of code instead of the one you posted...
VBA Code:
cboDateStart.List = Evaluate("TEXT(A2:A32,""dd/mm/yyyy"")")
 
Upvote 0
Thanks, the range A2:A32 are stored as dates not text, asume this will effect the code you have supplied?
You Excel default date is dd/mm/yyyy but VBA automatically convert it to mm/dd/yyyy format (American style). That is why the date you see is mm/dd/yyyy. By converting to text just to display in ComboBox in right format. When you want to use date from that ComboBox, you can easily convert it back using DateValue. I believe it will follow your Excel default format for its value.
 
Upvote 0
Try using this line of code instead of the one you posted...
VBA Code:
cboDateStart.List = Evaluate("TEXT(A2:A32,""dd/mm/yyyy"")")
This did not work in my Excel ?
Runtime error 381....Could not set list property. Invalid property array index
 
Upvote 0
This did not work in my Excel ?
Runtime error 381....Could not set list property. Invalid property array index
I tested it before posting it and again just now and it worked for me both times. Also, I am not sure how the code line I posted could possibly raise that particular error as Excel/VBA automatically starts its range arrays at 1.
 
Upvote 0
I tested it before posting it and again just now and it worked for me both times. Also, I am not sure how the code line I posted could possibly raise that particular error as Excel/VBA automatically starts its range arrays at 1.
I see. I'm not sure why. I just copy your code and change the range to range to mine...

Thanks for confirmation. I'll try to find out what's wrong with mine later. ;)
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top