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