Why does my VBA User form Listbox date reformat to mm/dd/yyyy ?

Hi All,

I'm developing an Excel User form which holds a few text boxes, some function buttons and a Listbox to manage records on a worksheet called 'Orders'.

The problem is this : when I update a record in my User form, the Order date in the textbox and also in the Listbox changes from dd/mm/yyyy to mm/dd/yyyy.

It also changes the format of the date on the underlying data sheet in the same way. I've tried reformatting the date just before I commit the update but it doesn't seem to make any difference.

Can anyone please help ?

I've attached a cutdown version (in data at least) of the application.

Much appreciated
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TocogroupAuthor Commented:
One thing I should have mentioned is that this only seems to affect dates with a dd of 10 or less. The first 3 entries in the ListBox show this affect - in which the dd and mm parts of the date are reversed to mm and dd.
SteveCost AccountantCommented:
VBA will try to convert dates using American format. It just does and it is a pain.
To get round this I tend to use dates in format 2-3-4 to ensure nothing goes wrong.
By 2-3-4 format I mean dd-mmm-yyyy

myDate = Format(Date,"dd-mmm-yyyy")

Open in new window

This has no issue within VBA or Excel/Access/Sql etc etc as the date is 01-Jan-2013 format which is always correct across all systems.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Thanks for that. It's resolved the problem although I'm still baffled as to why VBA only applies the American format for dates with a day of 9 or less.
Any thoughts ?
SteveCost AccountantCommented:
it is likely only applying to days 12 and less...

This is because 13/1/2013 can only be interpreted as 13th January 2013 (UK format).
Where 12/1/2013 can be 1st of December 2013 (US Format).
VBA works with US format first, then UK if not a valid US date, hence the difference.
TocogroupAuthor Commented:
Many thanks for your explanation and the solution.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.