Textboxs as dates

[funkymonkUK]

HI I got a userform which has textboxes however I want the textboxs to only enter in dates in the format as dd/mm/yyyy. I the user must enter in that format and the textbox should automatic change it to that format once the user exits the textbox. I then want the user to click on the commandbutton who will then check the 3 dates what the user entered to ensure that they are one date is after the others.

Hope someone can help me soon.

[mangesh_yadav]

first part:

Private Sub TextBox1_Change()

TextBox1.MaxLength = 10

If Len(TextBox1) = 2 Or Len(TextBox1) = 5 Then
If Right(TextBox1, 2) > 31 Then
MsgBox "Invalid date. Please enter correct value for dd"
TextBox1.SetFocus
Exit Sub
Else
TextBox1 = TextBox1 & "/"
End If
End If

If Len(TextBox1) = 5 Then
If Right(TextBox1, 2) > 12 Then
MsgBox "Invalid date. Please enter correct value for mm"
TextBox1.SetFocus
Exit Sub
Else
TextBox1 = TextBox1 & "/"
End If
End If


End Sub



Second part top check if the dates are in an order:

Private Sub CommandButton1_Click()

dt1 = DateValue(Mid(TextBox1, 4, 2) & "/" & Left(TextBox1, 2) & "/" & Right(TextBox1, 4))
dt2 = DateValue(Mid(TextBox2, 4, 2) & "/" & Left(TextBox2, 2) & "/" & Right(TextBox2, 4))
dt3 = DateValue(Mid(TextBox3, 4, 2) & "/" & Left(TextBox3, 2) & "/" & Right(TextBox3, 4))


If dt1 > dt2 Or dt2 > dt3 Then
MsgBox "Dates are not in order"
End If

End Sub

[funkymonkUK]

do i need to do the first part to each textbox? I tried it on one textbox works great except it still allows the user to enter in anything higher than 12 months. is there not maybe a property on the textbox itself?

[mangesh_yadav]

if there is a property, i don't know. And you have to do it for each textbox. You can add more validations.

and as for the error you mentioned, there was a small error in the previous post:

Private Sub TextBox1_Change()

TextBox1.MaxLength = 10

If Len(TextBox1) = 2 Then
If Right(TextBox1, 2) > 31 Then
MsgBox "Invalid date. Please enter correct value for dd"
TextBox1.SetFocus
Exit Sub
Else
TextBox1 = TextBox1 & "/"
End If
End If

If Len(TextBox1) = 5 Then
If Right(TextBox1, 2) > 12 Then
MsgBox "Invalid date. Please enter correct value for mm"
TextBox1.SetFocus
Exit Sub
Else
TextBox1 = TextBox1 & "/"
End If
End If


End Sub


Mangesh

[funkymonkUK]

thanks is there a way to check the year as well to be in the format yy and have done it however i get a type mismatch when i click the commandbutton1 it selects the first line of the code have no idea way? any ideas?

[mangesh_yadav]

because the command button is trying to read the last 4 characters as year and finds a "/" when you enter as yy and so gives the error.

- Mangesh