Excel User Form Validation 1

[shadyness]

I usually work in different applications, usually in VB6 actually, and if I want to check if an input field is numeric, I usually use the IsNumeric Function. However, in an excel user form I made the code:

[red]
If IsNumeric(Me.TextBox1.Value And Me.TextBox2.Value _
             And Me.TextBox3.Value And Me.TextBox4.Value And Me.TextBox5.Value _
             And Me.TextBox6.Value And Me.TextBox7.Value And Me.TextBox8.Value _
             And Me.TextBox9.Value And Me.TextBox10.Value And Me.TextBox11.Value _
             And Me.TextBox12.Value And Me.TextBox13.Value And Me.TextBox14.Value _
             ) Then

[/red]

produces a Type Mis-Match error if one field is alpha. The Else statement after all of this is a simple message box instructing a re-inspection of the fields. This has worked many times in my previous VB projects. So what am I missing that I need to do in excel?

Thanks in advance for any assistance. . . .


<== Some people say they are afraid of heights. With me its table widths. ==>

 

[Zathras]

Not sure, but I think the &quot;And&quot; is causing a bit-wise comparison with each value before evaluating the IsNumeric function.

I think what you need is

If IsNumeric(Me.TextBox1.Value) _
 And IsNumeric(Me.TextBox2.Value) _
 And IsNumeric(Me.TextBox3.Value) _
 And IsNumeric(Me.TextBox4.Value) _
 And IsNumeric(Me.TextBox5.Value) _
 And IsNumeric(Me.TextBox6.Value) _
 And IsNumeric(Me.TextBox7.Value) _
 And IsNumeric(Me.TextBox8.Value) _
 And IsNumeric(Me.TextBox9.Value) _
 And IsNumeric(Me.TextBox10.Value) _
 And IsNumeric(Me.TextBox11.Value) _
 And IsNumeric(Me.TextBox12.Value) _
 And IsNumeric(Me.TextBox13.Value) _
 And IsNumeric(Me.TextBox14.Value) Then

You could probably set up a loop through the controls collection on the form to simplify the code a bit.

 

[shadyness]

Excellent call on the comparison. That worked perfect. I was trying to avoid doing each one individually, but now I might as well give each one its own error message.

Thanks For The Help!

--Ian <== Some people say they are afraid of heights. With me its table widths. ==>