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

Tocogroup
Tocogroup used Ask the Experts™
on
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
Toco
Orders-Application-v1.0.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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.
Cost Accountant
Top Expert 2012
Commented:
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

Example:
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.

Author

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 Accountant
Top Expert 2012

Commented:
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.

Author

Commented:
Many thanks for your explanation and the solution.

Cheers
Toco

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial