Date posting issue from Form

[collis47]

When my form posts the data from a form to my data sheet it is
changing the date. here is my code to post the information:

Private Sub CommandOK_Click()
' Make Sure Sheet1 is active
Sheets("Data").Activate

' Determine next empty row
nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1


' Make sure serial number entered
If ComboSerialNo.Text = "" Then
MsgBox "You Must Enter A Serial Number!"
ComboSerialNo.SetFocus
Exit Sub
End If


' Check if Serial Number Has Previous Entry
If ComboSerialNo.Text = notinlist Then MsgBox ("Warning Serial
Number Has No Previous Entrys")

' Transfer SerialNum
Cells(nextrow, 1) = ComboSerialNo.Text

' Transfer Date
Cells(nextrow, 2) = ComboDate.Text

' Transfer On/Off Hire
If OptionOn Then Cells(nextrow, 3) = "1"



OptionOn.SetFocus

End Sub

what is happening is that the date is entered as dd/mm/yyyy or dd/mm/
yy but when it posts to the sheet, it is changing the format to mm/dd/
yyyy eg. if i entered a date of 01/03/2007 it would post as
03/01/2007, but the format of the date on the sheet is still dd/mm/
yyyy so the date is completly stuffed up, any ideas?

 

[Dave Peterson]

It would scare me to just blindly drop a string that looks like a date into a
cell. (Excel will see it just like you typed it in--matching the same as the
short date format in the user's window regional settings.)

You could parse it the way you want using a combination of mid, left, rights,
but I think I'd either use a calendar control:

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

Or ask for the day, year, month in separate controls (spinners/comboboxes/etc).

Then use something like:
with somecell
.value = dateserial(cboYear.text,cboMonth.text,cboDay.text)
.numberformat = "dd/mm/yyyy"
end with