Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
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 "Carlee" wrote: 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! -- Carlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
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 -- Carlee "JLatham" wrote: 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 "Carlee" wrote: 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! -- Carlee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling
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. "Carlee" wrote: 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 -- Carlee "JLatham" wrote: 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 "Carlee" wrote: 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! -- Carlee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error handling | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling. | Excel Programming | |||
Error Handling | Excel Programming | |||
Error handling with a handling routine | Excel Programming |