Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate a date
Hi,
I would like to validate that something that the user have writen in a text-box in a user form realy is a date and that is has a proper format. Does any one have any ideas ? Thank you ! Melker |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate a date
Hi Melker
A user should really be allowed to enter a date in whichever format she's comfortable with; Excel and VBA can understand most of them and a date is a date is a date. Try this, it runs when the textbox looses focus. It accepts "Feb 1", "2.5 03" and whatever, if it's a valid date then it reformats to mm/dd/yyyy format, otherwise it clears and beeps: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim Dt As Date On Error Resume Next Dt = DateValue(TextBox1.Text) If Dt DateSerial(1950, 1, 1) Then TextBox1.Text = Format$(Dt, "mm/dd/yyyy") Else TextBox1.Text = "" Beep End If End Sub HTH. Best wishes Harald "Melker" skrev i melding ... Hi, I would like to validate that something that the user have writen in a text-box in a user form realy is a date and that is has a proper format. Does any one have any ideas ? Thank you ! Melker |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate a date
Thank you,
I will try that! I would however want to see if i can not also "test" the date. With that i mean that 2004-02-31 or 31/2/2004 would not be valid... Is there a easy way ? Regards Melker "Harald Staff" skrev i meddelandet ... Hi Melker A user should really be allowed to enter a date in whichever format she's comfortable with; Excel and VBA can understand most of them and a date is a date is a date. Try this, it runs when the textbox looses focus. It accepts "Feb 1", "2.5 03" and whatever, if it's a valid date then it reformats to mm/dd/yyyy format, otherwise it clears and beeps: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim Dt As Date On Error Resume Next Dt = DateValue(TextBox1.Text) If Dt DateSerial(1950, 1, 1) Then TextBox1.Text = Format$(Dt, "mm/dd/yyyy") Else TextBox1.Text = "" Beep End If End Sub HTH. Best wishes Harald "Melker" skrev i melding ... Hi, I would like to validate that something that the user have writen in a text-box in a user form realy is a date and that is has a proper format. Does any one have any ideas ? Thank you ! Melker |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate a date
Tests first, complaints later ;-)
Ok, we may have different behaviors caused by international date formatting differences, that is a pretty complex issue. But here in Norway february 31st is invalid to my code and the entry is discarded. Likewise, feb 29 2000 is accepted but feb 29 2100 isn't. You ought to be pretty safe. HTH. Best wishes Harald "Melker" skrev i melding ... Thank you, I will try that! I would however want to see if i can not also "test" the date. With that i mean that 2004-02-31 or 31/2/2004 would not be valid... Is there a easy way ? Regards Melker "Harald Staff" skrev i meddelandet ... Hi Melker A user should really be allowed to enter a date in whichever format she's comfortable with; Excel and VBA can understand most of them and a date is a date is a date. Try this, it runs when the textbox looses focus. It accepts "Feb 1", "2.5 03" and whatever, if it's a valid date then it reformats to mm/dd/yyyy format, otherwise it clears and beeps: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim Dt As Date On Error Resume Next Dt = DateValue(TextBox1.Text) If Dt DateSerial(1950, 1, 1) Then TextBox1.Text = Format$(Dt, "mm/dd/yyyy") Else TextBox1.Text = "" Beep End If End Sub HTH. Best wishes Harald "Melker" skrev i melding ... Hi, I would like to validate that something that the user have writen in a text-box in a user form realy is a date and that is has a proper format. Does any one have any ideas ? Thank you ! Melker |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate a date
Great,
Jag provar det ! Tusen tack ! Melker "Harald Staff" skrev i meddelandet ... Tests first, complaints later ;-) Ok, we may have different behaviors caused by international date formatting differences, that is a pretty complex issue. But here in Norway february 31st is invalid to my code and the entry is discarded. Likewise, feb 29 2000 is accepted but feb 29 2100 isn't. You ought to be pretty safe. HTH. Best wishes Harald "Melker" skrev i melding ... Thank you, I will try that! I would however want to see if i can not also "test" the date. With that i mean that 2004-02-31 or 31/2/2004 would not be valid... Is there a easy way ? Regards Melker "Harald Staff" skrev i meddelandet ... Hi Melker A user should really be allowed to enter a date in whichever format she's comfortable with; Excel and VBA can understand most of them and a date is a date is a date. Try this, it runs when the textbox looses focus. It accepts "Feb 1", "2.5 03" and whatever, if it's a valid date then it reformats to mm/dd/yyyy format, otherwise it clears and beeps: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim Dt As Date On Error Resume Next Dt = DateValue(TextBox1.Text) If Dt DateSerial(1950, 1, 1) Then TextBox1.Text = Format$(Dt, "mm/dd/yyyy") Else TextBox1.Text = "" Beep End If End Sub HTH. Best wishes Harald "Melker" skrev i melding ... Hi, I would like to validate that something that the user have writen in a text-box in a user form realy is a date and that is has a proper format. Does any one have any ideas ? Thank you ! Melker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
validate date entered | Excel Worksheet Functions | |||
Validate date entry in a text box. | Excel Discussion (Misc queries) | |||
validate alternate day date | Excel Discussion (Misc queries) | |||
validate a date range | Excel Discussion (Misc queries) | |||
Get Day from Date to Validate | Excel Discussion (Misc queries) |