Combo list to format date dd/mm/yyyy

inkbird1

New Member
Joined
Apr 21, 2020
Messages
44
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,878
Office Version
  1. 2010
Platform
  1. Windows
Try using this line of code instead of the one you posted...
VBA Code:
cboDateStart.List = Evaluate("TEXT(A2:A32,""dd/mm/yyyy"")")
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,012
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,012
Office Version
  1. 2016
Platform
  1. Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,878
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,012
Office Version
  1. 2016
Platform
  1. Windows
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,946
Members
416,143
Latest member
JoyceMB

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
Top