Userform, Textbox data validation

[tqm1]

Dear Experts

There are two textboxes in userform.

I want textbox1 to accept only Date value in format "dd-mm-yy"
Textbox2 should accept only logical vaue "Y" or "N"

Please help

[Bob Phillips]

On the first, just test it for a date on exit.

On the second, use a checkbox, it can only be set or not set.

[geekgirlau]

With the first textbox, you need to add some code to the BeforeUpdate event for the control:


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(Me.TextBox1) Then
MsgBox "Please enter a valid date"
Cancel = True
End If
End Sub


The format of the date doesn't really matter, as you can manipulate this when you need to use the date somewhere.

With the second, you are better off using a checkbox, as this will only accept true or false.

[geekgirlau]

Pipped at the post!

[Spageddie]

Here is a quick sample on one way to do this - very simple method though.
there are better options such as IsDate, look for it in the help files
It will also format to ensure you have the "dd-mm-yy" for even if the user enters "d-m-yy"

Private Sub TextBox1_AfterUpdate()
Dim iDate As String
Dim iDays, iMnths, iYrs As String
Dim i As Integer

iDate = TextBox1.Text
i = InStr(1, iDate, "-", vbTextCompare)
iDays = Format(Left(iDate, (i - 1)), "###00")
iDate = Right(iDate, Len(iDate) - i)

i = InStr(1, iDate, "-", vbTextCompare)
iMnths = Format(Left(iDate, (i - 1)), "###00")
iDate = Right(iDate, Len(iDate) - i)
iYrs = Format(iDate, "###00")


If iDays > 31 Then
MsgBox "You not entered the correct date format" & vbCrLf & "Please enter the date as ""dd-mm-yy"""
TextBox1.Text = ""
Exit Sub
ElseIf iMnths > 12 Then
MsgBox "You not entered the correct date format" & vbCrLf & "Please enter the date as ""dd-mm-yy"""
TextBox1.Text = ""
Exit Sub
ElseIf CInt(iYrs) < 15 And CInt(iYrs) > 80 Then
MsgBox "Please enter a year between 1980 & 2015"
TextBox1.Text = ""
Exit Sub
End If

TextBox1.Text = iDays & "-" & iMnths & "-" & iYrs

End Sub