Immediate textbox validation question

[Sam Kuo]

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

 

[Shasur]

Hi Sam

Can you try placing your code in txtARI_AfterUpdate event

Cheers

 

[Sam Kuo]

Hi Shasur

I tried txtARI_AfterUpdate() event as your suggested, and
replace
Cancel = True
with
.txtK.SetFocus
.txtARI.SetFocus
so the focus sets to another textbox txtK in the same page as the validation
txtARI, then finally set back to txtARI (this is the tip I get from Dick
Kusleika in an earlier post, but it didn't to work for me?)

The modified code fires the message box OK, even if I click on a different
multipage tab straight after a false entry - this is good. But the focus
stays on the multipage tab that I just clicked, and not back to txtARI after
closing the message box.

Can you see why this is?

Sam

Hi Sam

Can you try placing your code in txtARI_AfterUpdate event

Cheers


--
http://vbadud.blogspot.com

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