PDA

View Full Version : Validate Data entered in UserForm



emailing
11-28-2007, 05:42 AM
Hi everybody, looking for some support on the following:

A user form is used to enter data into a sheet. With

Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(txtDate.Text) Then
MsgBox "Enter valid date format: dd.mm.yy"
'Cancel = True
End If
End Sub

only data that is recognised as Date is allowed in the form. However, is there a possibility to specify the allowed format of the date even more, i.e. that only the format dd.mm.yy is accepted by the control? Furthermore, if the year yy is entered, Excel asks if I mean 19xx or 20xx. Can I standardise that somehow so that everything with 9y is automatically converted on the sheet to 199y, and everything else to 20yy?

Initially I was trying to use the normal Cells Formatting and Data Validation tools from Excel, but it seems that works only properly when I enter the data directly on the sheet, not when I enter them with a user form. Or is there a way to use them with a user form?

Thanks for your help in advance!

Bob Phillips
11-28-2007, 05:56 AM
I have been trying trying to build a reliable textbox mask for years, nowhere near yet.

You could parse the field on exit



Private Sub TextBox1_AfterUpdate()
Dim mpError As String

With Me.TextBox1

If Not IsDate(.Text) Then

mpError = mpError & "Invalid date" & vbNewLine
End If


If Len(.Text) <> 8 Then

mpError = mpError & "Wrong length" & vbNewLine
End If

If InStr(.Text, "/") <> 3 Then

mpError = mpError & "No/misplaced first dot" & vbNewLine
End If

If InStr(4, .Text, "/") <> 6 Then

mpError = mpError & "No/misplaced second dot" & vbNewLine
End If
End With

MsgBox mpError
End Sub