Hi all,

I have a problem with Excel switching dates from dd/mm to mm/dd. I a
using Excel 2003.

- Dates are stored on the worksheet 'REF' in dd/mm/yy format
- Those same dates are the rowsource for a combobox in a userform
- When the user clicks 'Submit' on the userform, the date is written t
the next blank cell on the worksheet 'DATA'. This cell is also formatte
as dd/mm/yy.

For some reason, Excel swaps some date formats around when written
This only occurs where it is possible to logically do so.

i.e. 01/08/06 (dd/mm/yy) becomes 08/01/06 (mm/dd/yy)
17/01/06 (dd/mm/yy) remains 17/01/06 as 01/17/06 is not a valid date.

With the example above, the serial value is 38930. When it comes out
reversed, the serial value actually changes 38725. So in actual fact
not just a change of formatting but a complete change in the date!

I have pasted my relevant code below -

Private Sub cboDate_Change()

With Me.cboDate
.Value = Format(.Value, \"dd/mm/yyyy\")
End With

End Sub

Private Sub submitbutton_Click()
Dim nextR As String
Dim newR As String

Sheets(\"Data\").Select
Range(\"A1\").Select

Lastrow = Cells(Rows.Count, \"A\").End(xlUp).Row
nextR = Lastrow + 1

newR = \"A\" & nextR
Range(newR).Select

ActiveCell.Value = cboDate.Value

ActiveCell.Offset(0, 3).Select
ActiveCell.Value = cboConsultant.Value

ActiveCell.Offset(0, 3).Select
ActiveCell.Value = cboStart.Value

ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cboFinish.Value

ActiveCell.Offset(0, 2).Select
ActiveCell.Value = cboTask1.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = txtTask1.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = cboTask2.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = txtTask2.Value

End Sub

Could anyone help me out with this one? It has me baffled! System dat
formats check out, and Excel standard date is Australian.

Thanks

Ro

--
system
-----------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...fo&userid=2925
View this thread: http://www.excelforum.com/showthread.php?threadid=56654