I think xl will see you putting tmpstr in the cell and then it uses the windows
regional settings to see what order the mdy should be.

If you're settings are USA (mdy), then it'll try to make it a date using that.
Finally, it'll figure out what to show in the cell by the cell's format.

So if I plop 10/12/2003 into a cell (with my windows regional settings in mdy
order), I'll get Oct 12, 2003.

But if I do the same thing with dmy, I'll get Dec 10, 2003.

I'm not sure of a good way to force an unambiguous date using just one
textbox--maybe 3 (month, day & year???) or a calendar control???

But if you know you're expecting dd/mm/yy, maybe you could parse the string into
its pieces and then:

with no validity checks:

Option Explicit
Sub testme()

Dim TmpStr As String
Dim TmpArr As Variant

TmpStr = InputBox("dmy style")
TmpArr = Split97(TmpStr, "/")

Selection.Value = DateSerial(TmpArr(UBound(TmpArr)) + 2000, _
TmpArr(LBound(TmpArr) + 1), _
TmpArr(LBound(TmpArr)))
Selection.NumberFormat = "mmm dd, yyyy" 'just for verification
End Sub

'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function


If you're using xl2k or higher, you can replace split97 with split and delete
the function. (Split was added in xl2k.)



Murray Taylor wrote:

i am having a bit of a problem with date format
manipulation. I capture a date from a text box on a form
and write it to a cell on a worksheet, in the process the
date switches from UK (dd/mm/yy) to US (mm/dd/yy) format,
despite the fact that it appears on the form in the UK
style and the cell in the worksheet has the UK date format
set. Any advice?

This is the code fragment if it is on any use:

A) tmpStr = Form1.Textbox1.text

B) Selection.Formula = tmpstr

Fragment (A) is in one procedure fragment (B) is in
another, no other manipulation takes place. I have even
tried using Format(tmpstr, "dd/mm/yy")

Murray

--

Dave Peterson