Error Handling

[Guest]

Hi,

Short of writing code for each on_exit event of each field on my form that
is of 'numeric' type, is there code that i can use to notify the user when
they have entered invalid data in a field? I have about 80 text boxes on a
multipage userform.

Any suggestions would be fantastic!

 

[Guest]

Write one Function in the form's code module that evaluates a value passed to
it as numeric and then simply call that function from each _Exit() routine -
passing it the value of the individual text box and if you need to test for a
valid range of numbers that varies from box to box, then pass it those limits
also?

Maybe something like this?

Private Sub TextBox1_Exit(ByVal Cancel _
As MSForms.ReturnBoolean)

Cancel = EvaluateText(Me!TextBox1.Text, 5, 10)
End Sub

Private Function EvaluateText(anyEntry As String, _
loLimit As Integer, upLimit As Integer) As Boolean

EvaluateText = True ' to set Cancel with
If Not IsNumeric(anyEntry) Then
'generic not-a-number message
MsgBox "Entry is not numeric"
Exit Function ' exit with True
End If
If Val(anyEntry) < loLimit Or Val(anyEntry) > upLimit Then
'invalid value entered message
MsgBox "Invalid Value Entered"
Exit Function ' exit with True
End If
EvaluateText = False ' passed tests, don't cancel
End Function

 

[Guest]

Thanks for the help. It is almost working!!!
I only needed the first part of the code you listed (checking for numeric
data). When i enter non-numeric data, the message fires, and i can correct
the entry.

Problem:
I cannot move off the field. The system will not allow me to move to any
other field. Any suggestions on how to proceed? Code provided below:


Private Function EvaluateText(anyEntry As String, _
loLimit As Integer, upLimit As Integer) As Boolean

EvaluateText = True ' to set Cancel with
If Not IsNumeric(anyEntry) Then
MsgBox "This field accepts numeric data only. Please revise your entry
and try again."
Cancel = True
Exit Function ' exit with True
End If


End Function

 

[Guest]

The way you've got the code setup, it always exits with a True condition,
which (if you wrote the _Exit() code same as I did) will always Cancel the
exit and get you stuck in the field. Since you don't need to check
upper/lower limits, we can do it all this way:

Private Sub TextBox1_Exit(ByVal Cancel _
As MSForms.ReturnBoolean)

Cancel = EvaluateText(Me!TextBox1.Text)
End Sub

Private Function EvaluateText(anyEntry As String) _
As Boolean

EvaluateText = False ' all is well
If Not IsNumeric(anyEntry) Then
MsgBox "This field accepts numeric data only. Please revise your entry
and try again."
EvaluateText = True ' to be copied to Cancel
Exit Function ' exit with True
End If
'if it makes it to here, it is numeric and
'EvaluateText will be False, which will NOT
'trigger the Cancel in the calling routine, so
'you should move on to the next control now.
End Function

The 2 boo-boo's in your code - for the way you've got it set up,
EvaluateText should initially be set to False. Actually, you don't even need
that first EvaluateText= statement, since the default condition is False, but
just change it as a kind of belt and suspenders deal.

Then within your If...Then evaluation you were setting Cancel to True and
the calling routine never sees that, it only sees the value of the Function
itself, so instead of setting Cancel = True in there, set EvaluateText = True.

As you can see from the new definition of EvaluateText() you should now only
pass one parameter from the _Exit event handlers - which I've shown above.

Also, the editor here is probably going to break up the MsgBox text, so
watch out if you cut and paste directly from this post.