TextBox validation code

[tkraju via OfficeKB.com]

In my userform I have 2 TextBoxes.
I nedd code for
validation of TextBox2 is entry : it should be Date,should be current month
and current year date,entry date should be greater than or equal to textbox1.
Thank You.

 

[OssieMac]

Try the following. Let me know if not working as you want it to but let me
know what locale date format you are using.

Private Sub TextBox2_AfterUpdate()

Dim bolValid As Boolean
Dim dateValid As Date

On Error Resume Next
dateValid = DateValue(Me.TextBox2)
On Error GoTo 0

If dateValid > 0 Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid date format in TextBox2" _
& vbCrLf & "Must be d/m/y format"
Exit Sub
End If

If Year(dateValid) = Year(Date) Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid Year in TextBox2" & _
vbCrLf & "Must be " & Year(Date)
Exit Sub
End If

If Month(dateValid) = Month(Date) Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid Month in TextBox2" & _
vbCrLf & "Must be " & Month(Date)
Exit Sub
End If

If dateValid >= DateValue(Me.TextBox1) Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid date in TextBox2" & _
vbCrLf & "Must be >= to TextBox1"
Exit Sub
Exit Sub
End If

'You should not need following code
'Only there for testing
If bolValid Then
MsgBox "Date in TextBox2 is valid"
End If

End Sub

 

[tkraju via OfficeKB.com]

I have forgotten to mention,If validation does not meet any condition cursor
should go TextBox2 prmpt for correct entry.
I am using dd/mm/yy date format

 

[OssieMac]

Can only get the Cursor to set the focus back to TextBox2 if done in the Exit
Event. You will need all of the following code. Note the comments in the code.

I have now included code to allow the user to skip the textbox and leave
blank without invoking the validation. You will get problems even on closing
the form if the user for any reason needs to leave the field blank.

'Following Dim statement must be in Declarations _
area before any Subs or code.
Dim bolValid As Boolean

Private Sub TextBox2_AfterUpdate()

Dim dateValid As Date

'The following If/End If aborts validation _
if TextBox2 is empty. (Delete if not required)
If Me.TextBox2 = "" Then
Exit Sub
End If

On Error Resume Next
dateValid = DateValue(Me.TextBox2)
On Error GoTo 0

If dateValid > 0 Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid date format in TextBox2" _
& vbCrLf & "Must be dd/mm/yy format"
Exit Sub
End If

If Year(dateValid) = Year(Date) Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid Year in TextBox2" & _
vbCrLf & "Must be " & Year(Date)
Exit Sub
End If

If Month(dateValid) = Month(Date) Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid Month in TextBox2" & _
vbCrLf & "Must be " & Month(Date)
Exit Sub
End If

If dateValid >= DateValue(Me.TextBox1) Then
bolValid = True
Else
bolValid = False
MsgBox "Invalid date in TextBox2" & _
vbCrLf & "Must be >= to TextBox1"
Exit Sub
End If

End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Following line allows user to skip if leaving blank
'Remove [And Me.TextBox2 <> ""] if you don't want this
If bolValid = False And Me.TextBox2 <> "" Then
Cancel = True
Me.TextBox2.SetFocus
End If
End Sub

 

[Dave Peterson]

You have an active thread elsewhere.

In my userform I have 2 TextBoxes.
I nedd code for
validation of TextBox2 is entry : it should be Date,should be current month
and current year date,entry date should be greater than or equal to textbox1.
Thank You.