"Andrew" wrote:


I am using a date field in English format that is then transposing from English to American format for no apparent reason.
Everything starts ok in the English format of DD/MM/YYYY.

The date is entered on a form with

Private Sub UserForm_Initialize()
Dim Today As Date
Today = Date = 05/07/2005 ie 05 July 2005.

' displays on the userform field correctly using
txtOrderDate.Value = Today = "05/07/2005"
'or
txtOrderDate.Value = Format(Today, "dd/mm/yyyy") also = "05/07/2005"

Private Sub Write_the_value_to_a_spreadsheet
Cells(1, 1).Value = txtOrderDate.Value
'or
Cells(1, 1).Value = Format(txtOrderDate.Value, "dd/mm/yyyy")
The spreadsheet displays 07/05/2005 ie 07 May 2005.

But the format options for the cell are showing as Customised "dd/mm/yyyy"
If I change the format of that cell to show the month then May is shown - so the spreadsheet is reading it correctly as English format, so I guess it must be the VBA code that is storing it in American format. I cant see where the transposition is occuring nor how to fix it.

Any ideas ?

Thanks
Andrew

I've come up with a solution myself... may not be the most elegant, but it
works...

Private Sub TextBox1_afterupdate()
OrgDate = TextBox1

a = Len(OrgDate)
b = Application.WorksheetFunction.Find("/", OrgDate)
c = a - b
d = Right$(OrgDate, c)
e = Trim(Left$(d, Len(d) - Application.WorksheetFunction.Find("/", d)))
e = e * 1
g = b - 1
h = Right$(d, Len(d) - Application.WorksheetFunction.Find("/", d))


If e = 1 Then f = "-Jan-"
If e = 2 Then f = "-Feb-"
If e = 3 Then f = "-Mar-"
If e = 4 Then f = "-Apr-"
If e = 5 Then f = "-May-"
If e = 6 Then f = "-Jun-"
If e = 7 Then f = "-Jul-"
If e = 8 Then f = "-Aug-"
If e = 9 Then f = "-Sep-"
If e = 10 Then f = "-Oct-"
If e = 11 Then f = "-Nov-"
If e = 12 Then f = "-Dec-"

OrgDate = Left$(OrgDate, g) & f & h


End Sub

When Orgdate is applied to the spreadsheet the format selected there
converts it fine.

HTH

Black