Textboxs as dates

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

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?

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

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?

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

thanks it worked however is there a way to check the years so as the user types they can only enter in years as yy and not yyyy

Right in the beginning there's a line:
TextBox1.MaxLength = 10
which tells the box to accept 10 characters. change it to
TextBox1.MaxLength = 8

Note: Becareful as to how your system reads the YY date... as 20YY or 19YY

Mangesh

HI mangesh_yadav

After extensive testing I seem to have a problem with validation to ensure the dates are correct as it should be

Textbox1<textbox2<textbox3

however if the users puts the 31/05/05 and then in textbox2 puts in 01/06/05 is says that the dates are not in order as I think it is picking up the 31 and the 01 and is not likeing it any ideas?

Hi funkymonk,

Assuming you are using this code:

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

Textbox1<textbox2<textbox3

however if the users puts the 31/05/05 and then in textbox2 puts in 01/06/05 is says that the dates are not in order as I think it is picking up the 31 and the 01 and is not likeing it any ideas?

I tried the folloing code:

Sub test()

textbox1 = "31/05/05"
textbox2 = "01/06/05"

dt1 = DateValue(Mid(textbox1, 4, 2) & "/" & Left(textbox1, 2) & "/" & Right(textbox1, 2))
dt2 = DateValue(Mid(textbox2, 4, 2) & "/" & Left(textbox2, 2) & "/" & Right(textbox2, 2))

If dt1 > dt2 Then MsgBox "not in order"

End Sub


And it does not give error. Which means it is working. Please check if this is how it should work and let me know.

Another point: The code I gave you earlier, has 2 conditions, I hope the other one is not falling apart.

this is the code that I am using.

The below is the validation code which i am using this is run when a command botton is press. Please ntoe I have 4 textboxs or which 3 I use the validation eg. textbox5,7,8. Textbox 6 is not validated in this code.

dt1 = DateValue(Mid(TextBox5, 4, 2) & "/" & Left(TextBox5, 2) & "/" & Right(TextBox5, 4))
dt2 = DateValue(Mid(TextBox7, 4, 2) & "/" & Left(TextBox7, 2) & "/" & Right(TextBox7, 4))
dt3 = DateValue(Mid(TextBox8, 4, 2) & "/" & Left(TextBox8, 2) & "/" & Right(TextBox8, 4))

If dt1 > dt2 Or dt2 > dt3 Then
MsgBox "Dates are not in order please correct Section 5. Date of."
GoTo 1
End If

The below is the code that I am using while the user is inputting. I am using the below code for all 4 textboxes.

Private Sub Textbox6_change()
TextBox6.MaxLength = 10

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

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

the user inputs as dd/mm/yyyy, would you code not be working due to you having dd/mm/yy. i think it is saying while nothing can be higher than 31 so therefore if the date is 31 and the next date is 1 then it says it is not in order.

Hi Funkymonk,

Is it giving the error "not in order" when you are entering the second date halfway through. Then could you post the other code also where you check which dates are greater/lesser.

Mangesh

i change the code the code which i submitted just now to the below.

dt1 = DateValue(Left(TextBox5, 2) & "/" & Mid(TextBox5, 4, 2) & "/" & Right(TextBox5, 4))
dt2 = DateValue(Left(TextBox7, 2) & "/" & Mid(TextBox7, 4, 2) & "/" & Right(TextBox7, 4))
dt3 = DateValue(Left(TextBox8, 2) & "/" & Mid(TextBox8, 4, 2) & "/" & Right(TextBox8, 4))

If dt1 > dt2 Or dt2 > dt3 Then
MsgBox "Dates are not in order please correct Section 5. Date of."
GoTo 1
End If

I looked at the code and realized that the data was putting into mm/dd/yyyy instead of what the user was entering as dd/mm/yyyy so i move the left(textbox5,2) which refers to the date as well as the mid(textbox5,2) around as that was refering to month, now it seems to be working 100%

Thanks for your help

So I presume that your problem is solved. If so, its nice to know. Thanks for the feedback.

Mangesh