Solved: Smart Error Handler

[russkie]

Hello,

I wanna have an error handler that handles according to the error that happened.

for example, lets consider 3 variables:
[VBA] dim bignumber as long
dim smallnumber as integer
dim someword as string
[/VBA]

now, instead of writing "IF" code by each instance that i use these variables, i want one error handler that, if lets say the user inputs into 'smallnumber' a very large number, the error handler will jump to a section where i can simply have a msgbox that tells them they did something wrong, change it, and continue with the macro... see, i dont want to make a million 'on error goto' sections. just one, that recognizes the specific problem, and allows me to change it. Am i making sense?

[Jacob Hilderbrand]

Well you can do a Select Case for the Err.Number. Each type of error will have a different number so you can identify what the error is and display an appropriate message.

[russkie]

well, the situation is like this, i have a userform with a buncha stuff that needs to be filed out.

item name
barcode
stock
cost price
sell price
etc...

Well the wonderful folks around the office seem to mix them up constantly, like put the barcode as the stock, giving us 9 billion of a stock, not cool.... I want to prevent this. So i was thinking, instead of having 3/4 IF's by each var, ill create an err handler that will recognize that, price, which is an integer, cannot be 9 billion, will get an error, recognize that 'price' is the problem, and ask the user to change it.

I cant do it with the error number becuase the same error will happen to 'stock' as with 'price' if the user sticks the barcode in there.

any ideas?
thnx alot.

[Killian]

you can use the textbox change event to force validation for each one with something like[VBA]Private Sub TextBox1_Change()
If TextBox1.Text > 999 Then
MsgBox "That's a very large number..."
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End If
End Sub[/VBA]I guess you can also test for valid entries (like strings for the item name etc).
If, rather than a bucha stuff, you gotta a whole heapa stuff, you mignt want to consider adding your textboxes to a class collection so you only have to write one lot of validation code

[russkie]

The first part, no, cant do that, ill make it short by saying it is a 'heap o stuff' and ill be swamped with writing tons of IF's.

However, that class text thingy sounds interesting, i dont really know about it.
Not with text boxes, or im not sure if its the same thing, but with inputboxes im calling in these values. So uh, can you tell me more about this classes? or direct me to where i can find out?

[Norie]

russkie

If Killian's first idea is no use I don't think classes will help.

Can you give some more information and perhaps post a sample workbook?

If you need to test for 'heap o stuff' for validation then there are various methods you could use with arrays/loops.

Also have you considered using comboboxes to restrict what the user can enter to a predefined list?

[Killian]

The first part, no, cant do that, ill make it short by saying it is a 'heap o stuff' and ill be swamped with writing tons of IF's.

However, that class text thingy sounds interesting, i dont really know about it.
Not with text boxes, or im not sure if its the same thing, but with inputboxes im calling in these values. So uh, can you tell me more about this classes? or direct me to where i can find out?

As Norie pointed out, if you can't use the textbox change event for validation, then using a custom class won't help (because you'd enable events for the class but use just the class' event code rather than having to have event code for each control)

But I (wrongly) assumed you were doing this with a userform - it appears you want to validate inputboxes...
I would suggest wrapping the inputbox call in your own function - that way for each one you can pass the fieldname and maximum value to display to the user in the prompt text. The inputbox will just loop until the user enters valid data.
Here's an example that does that - I've made it a little more flexible by dealing with both text and number input in the same function and the max value is optional.[VBA]Sub test()
'main routine where you want to use the returned input

Dim ReturnValue As Variant

'call the function with the required value name
'the input box type (1 for numbers, 2 for text, etc) and, optionally, a maximum value
ReturnValue = GetValidInput("Quantity", 1)

End Sub

'-------------------------------------------------------------------------------------
Function GetValidInput(strFieldName As String, lngType As Long, Optional varMax As Variant) As Variant
'note that a variant data type is required if you want whole and decimal numbers and text


Dim varInput As Variant
Dim strTitle As String
Dim strPrompt As String
Dim varDefault As Variant

strTitle = "Data entry"
'the prompt can remind the user which field they are entering
strPrompt = "Please enter " & strFieldName
'if you have a maximun value, then the user can be reminded of that also
If Not IsMissing(varMax) Then
strPrompt = strPrompt & vbLf & "(Maximum value: " & varMax & ")"
End If
varDefault = ""

If lngType = 1 Then 'numeric input
If IsMissing(varMax) Then 'if no max value is specified
Do
varInput = Application.InputBox(strPrompt, strTitle, varDefault, , , , , lngType)
varDefault = CStr(varInput)
Loop Until varDefault <> "" Or varInput = False
Else 'if a max value is specified
Do
varInput = Application.InputBox(strPrompt, strTitle, varDefault, , , , , lngType)
varDefault = CStr(varInput)
Loop Until varInput <= varMax Or varInput = False
End If
ElseIf lngType = 2 Then 'text input
Do
varInput = Application.InputBox(strPrompt, strTitle, varDefault, , , , , lngType)
varDefault = CStr(varInput)
Loop Until varInput <> "" Or varInput = False
End If

If Not varInput = False Then
GetValidInput = varInput
End If

End Function[/VBA]

[russkie]

thars gonna need some digesting for me

but what i have so far much appreciated thank you.