Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
Hi,
I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
Some pointers:
AFAIK IsDate should adapt itself to the current regional settings so you will be ok with this. You can use the Year function to extract just the year part of a date after calling IsDate to ensure it is actually a date. Be aware that things like IsDate("2/2003") will return True. Depending on your situation this may or may not be desirable and could require further refinement. -- Regards, Bill Lunney www.billlunney.com "David Goodall" wrote in message ... Hi, I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
David;
Because of being in a hurry just a brief and not complete answer to your " problem". A part of your "check" can be something like this. Here you check for a genuine dd/mm/yyyy dateformat. Assuming your textbox is called tbStartDate. If Not IsDate(.tbStartDate.Value) Then Response = MsgBox(ErrNoStartdate, vbCritical + vbOKOnly, Title) .tbStartDate.Value = "01/01/0001 ?" .tbStartDate.SetFocus Exit Sub End If Succes; The other part will be solved by other members I hope. Mark Rosenkrantz. More Excel ? www.rosenkrantz.nl or "David Goodall" wrote in message ... Hi, I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
In fact, here it is
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 If Not ValidDate(.Text) Then MsgBox "Please supply a valid date in dd/mm/yyyy format", vbCritical + vbOKOnly Cancel = True End If End With End Sub Function ValidDate(dte As String) If Len(dte) < 10 Or Mid(dte, 3, 1) < "/" Or Mid(dte, 6, 1) < "/" Then ValidDate = False Exit Function End If If Mid(dte, 4, 2) 12 Then ValidDate = False Exit Function End If If CLng(Right(dte, 4)) < 1900 Then ValidDate = False Exit Function End If ValidDate = True On Error GoTo invalid_date IsDate (CDate(dte)) Exit Function invalid_date: ValidDate = False End Function -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Bob Phillips" wrote in message ... David, Try this. It works okay with UK & US dates on my machine. If you want to eliminate US dates, just yell and I'll add to it. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 If Not ValidDate(.Text) Then MsgBox "Please supply a valid date in dd/mm/yyyy format", vbCritical + vbOKOnly Cancel = True End If End With End Sub Function ValidDate(dte As String) If Len(dte) < 10 Or Mid(dte, 3, 1) < "/" Or Mid(dte, 6, 1) < "/" Then ValidDate = False Exit Function End If If CLng(Right(dte, 4)) < 1900 Then ValidDate = False Exit Function End If ValidDate = True On Error GoTo invalid_date IsDate (CDate(dte)) Exit Function invalid_date: ValidDate = False End Function -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "David Goodall" wrote in message ... Hi, I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
Hi David
In my opinion users should never be bothered with proper input formats and annoying messageboxes. The code below formats and validate all common date inputs and converts to the desired format without noise and interruptions. In addition to all date entries that regional settings recognize, you can use Jan 12 and 4 to 8 digit non-delimited numbers (in european ddmmyyyy order) like 1201 120195 12011995 Here's the code, I use it all the time in my projects. Edit/remove the last age check part, it's a demo for you only: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim iDay As Integer Dim iMonth As Integer Dim iYear As Integer Dim dtDate As Date With TextBox1 If Val(.Text) = 1000000 Then iDay = Int(Val(.Text) / 1000000) iMonth = Int(Val(.Text) / 10000) Mod 100 iYear = Val(.Text) Mod 10000 dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") ElseIf Val(.Text) 10000 Then iDay = Int(Val(.Text) / 10000) iMonth = Int(Val(.Text) / 100) Mod 100 iYear = Val(.Text) Mod 100 If iYear 30 Then iYear = iYear + 1900 Else iYear = iYear + 2000 End If dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") ElseIf Val(.Text) 100 Then iDay = Int(Val(.Text) / 100) iMonth = Val(.Text) Mod 100 iYear = Year(Date) dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") Else If IsDate(.Text) = False Then .Text = "" Else dtDate = DateValue(.Text) If dtDate < 10 Then .Text = "" Else .Text = Format$(dtDate, "dd/mm/yyyy") End If End If End If End With 'edit / remove from he If dtDate 1000 Then Select Case Year(Date) - Year(dtDate) Case 0 To 13 'replace with proper actions: MsgBox "too young" Case 80 To 999 MsgBox "too old" Case Else End Select End If End Sub -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please. "David Goodall" skrev i melding ... Hi, I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
Nice stuff. However, I noticed a good feature in this
routine. If one was to provide an input like 151560 in the textbox, then this routine "correctly" interprets it as 15th day of 15th month of the year 1960. And since there are only 12 months in 1960, the 13th month is considered as January 1961, 14th month as February 1961, and 15th month as March 1961. Thus, to make matters short, 151560 results in 15/03/1961 as output of this routine. -----Original Message----- Hi David In my opinion users should never be bothered with proper input formats and annoying messageboxes. The code below formats and validate all common date inputs and converts to the desired format without noise and interruptions. In addition to all date entries that regional settings recognize, you can use Jan 12 and 4 to 8 digit non-delimited numbers (in european ddmmyyyy order) like 1201 120195 12011995 Here's the code, I use it all the time in my projects. Edit/remove the last age check part, it's a demo for you only: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim iDay As Integer Dim iMonth As Integer Dim iYear As Integer Dim dtDate As Date With TextBox1 If Val(.Text) = 1000000 Then iDay = Int(Val(.Text) / 1000000) iMonth = Int(Val(.Text) / 10000) Mod 100 iYear = Val(.Text) Mod 10000 dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") ElseIf Val(.Text) 10000 Then iDay = Int(Val(.Text) / 10000) iMonth = Int(Val(.Text) / 100) Mod 100 iYear = Val(.Text) Mod 100 If iYear 30 Then iYear = iYear + 1900 Else iYear = iYear + 2000 End If dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") ElseIf Val(.Text) 100 Then iDay = Int(Val(.Text) / 100) iMonth = Val(.Text) Mod 100 iYear = Year(Date) dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") Else If IsDate(.Text) = False Then .Text = "" Else dtDate = DateValue(.Text) If dtDate < 10 Then .Text = "" Else .Text = Format$(dtDate, "dd/mm/yyyy") End If End If End If End With 'edit / remove from he If dtDate 1000 Then Select Case Year(Date) - Year(dtDate) Case 0 To 13 'replace with proper actions: MsgBox "too young" Case 80 To 999 MsgBox "too old" Case Else End Select End If End Sub -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please. "David Goodall" skrev i melding news:FFpZa.17140$R6.1487651@newsfep2- win.server.ntli.net... Hi, I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Date validation
I have seen this discussion frequently and because of it have opted to go
for a different solution. Not having a calandar control, I use 3 comboboxes - one for day, one for month, and one for year. Than the code combines the input into a date. steve "Akshay Bakhai" wrote in message ... Nice stuff. However, I noticed a good feature in this routine. If one was to provide an input like 151560 in the textbox, then this routine "correctly" interprets it as 15th day of 15th month of the year 1960. And since there are only 12 months in 1960, the 13th month is considered as January 1961, 14th month as February 1961, and 15th month as March 1961. Thus, to make matters short, 151560 results in 15/03/1961 as output of this routine. -----Original Message----- Hi David In my opinion users should never be bothered with proper input formats and annoying messageboxes. The code below formats and validate all common date inputs and converts to the desired format without noise and interruptions. In addition to all date entries that regional settings recognize, you can use Jan 12 and 4 to 8 digit non-delimited numbers (in european ddmmyyyy order) like 1201 120195 12011995 Here's the code, I use it all the time in my projects. Edit/remove the last age check part, it's a demo for you only: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim iDay As Integer Dim iMonth As Integer Dim iYear As Integer Dim dtDate As Date With TextBox1 If Val(.Text) = 1000000 Then iDay = Int(Val(.Text) / 1000000) iMonth = Int(Val(.Text) / 10000) Mod 100 iYear = Val(.Text) Mod 10000 dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") ElseIf Val(.Text) 10000 Then iDay = Int(Val(.Text) / 10000) iMonth = Int(Val(.Text) / 100) Mod 100 iYear = Val(.Text) Mod 100 If iYear 30 Then iYear = iYear + 1900 Else iYear = iYear + 2000 End If dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") ElseIf Val(.Text) 100 Then iDay = Int(Val(.Text) / 100) iMonth = Val(.Text) Mod 100 iYear = Year(Date) dtDate = DateSerial(iYear, iMonth, iDay) .Text = Format$(dtDate, "dd/mm/yyyy") Else If IsDate(.Text) = False Then .Text = "" Else dtDate = DateValue(.Text) If dtDate < 10 Then .Text = "" Else .Text = Format$(dtDate, "dd/mm/yyyy") End If End If End If End With 'edit / remove from he If dtDate 1000 Then Select Case Year(Date) - Year(dtDate) Case 0 To 13 'replace with proper actions: MsgBox "too young" Case 80 To 999 MsgBox "too old" Case Else End Select End If End Sub -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please. "David Goodall" skrev i melding news:FFpZa.17140$R6.1487651@newsfep2- win.server.ntli.net... Hi, I'm currently developing a userform which has a text box that prompts the user for a date of birth. I would like to validate the input as a correct date of birth. ie not born before 1900 and in correct dd/mm/yyyy format. I've had a look at the the isdate function but I'm not sure if it can handle the British date format. Any help would be greatly appreciated. David . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format for Userform TextBox | New Users to Excel | |||
userform date issue | Excel Worksheet Functions | |||
Restrict date format for UserForm | Excel Discussion (Misc queries) | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Userform Textbox in Password Validation | Excel Programming |