Thread
:
Date posting issue from Form
View Single Post
#
2
Posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
Date posting issue from Form
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
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
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson