Immediate textbox validation question
 

Hi

My objective is to have a textbox that performs immediate validation check
after entry. And if the condition isn't met, prompt message box, clear the
textbox, and set the focus back to the textbox. Below is my attempt so far.

The problem with this code is that, after a false entry, the code fires only
when the focus is set to a control in the SAME frame or page (i.e. in the
same tab order window).

For example, after a false entry in the validation textbox txtARI, if I
click on another textbox in a different frame, the code doesn't fire until I
click on another control in the same frame as the validation textbox txtARI.
Such "delayed" validation is rather confusing for user.

I thought about using textbox Change event so the code fires as soon as the
value changes, but struggle to include the required validation condition,
which needs the textbox value to be between 50 and 130.

Any ideas?


Private Sub txtARI_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim stxtARI As String

With Me
stxtARI = .txtARI.Value
If Not IsNumber(stxtARI) Then
If stxtARI = "" Then Exit Sub 'accept nothing as a valid number
.txtARI.BackColor = &HFFFF&
MsgBox "Please enter a value between 50 and 130 (Refer Figure
A.1)", _
vbOKOnly + vbExclamation, "Invalid Input"
.txtARI.Value = ""
.txtARI.BackColor = &H80000005
Cancel = True
Exit Sub
End If

If stxtARI < 50 Or stxtARI 130 Then
.txtARI.BackColor = &HFFFF&
MsgBox "Please enter a value between 50 and 130 (Refer Figure
A.1)", _
vbOKOnly + vbExclamation, "Invalid Input"
.txtARI.Value = ""
.txtARI.BackColor = &H80000005
Cancel = True
Else
.txtARI.BackColor = &H80000005
End If
End With
End Sub

Function IsNumber(ByVal Value As String) As Boolean
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) 0 And Value < "." And _
Value < vbNullString
End Function