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?

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



(E-Mail Removed) wrote:
>
> 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