Date format when using combo box

[Mikey May]

I am using a combo box to fill in data in a spreadsheet.

One part of the data is the date. I have set the required
cells to the following format dd/mm/yy, but when a date is
input via the combo box sometimes the month and date are
swapped around, eg 11/05/03 (11th May 03) is interpretted
as 05/11/03 (5th Nov 03). other dates eg 23/06/03 are
interpretted correctly.

My Control Panel setting are set for the UK.

Also, I want to only allow input in this box as a date
format. if it is not recognised as a date eg 56/85/09 it
will ask for another input.

Any ideas?

 

[Tom Ogilvy]

Excel is US centric, so in most cases, if it can interpret a date as US
format, it will.

Cdate will observer your regional settings, so you need to convert your date
string to a date serial with Cdate

Dim dtVal as Date
Dim sStr as String

dtVal = 0
Do
sStr = InputBox("Enter Date")
Loop Until isdate(sStr) or sStr = ""
if sSTr = "" then exit sub
dtVal = cDate(sStr)
' or

Range("A1").Value = cDate(sStr)
Range("A1").NumberFormat = "dd/mm/yyyy"