VBA Not Equal Statement

[hayden]

Hello,

I'm trying to write a vba statement to check that the user has entered an integer in my userform text box between 3 and 6. This is what I have so far:

If TestNumBox.value <> 3 Or TestNumBox <> 4 Or TestNumBox <> 5 Or TestNumBox <> 6 Then
'TestNumBox is not an integer between 3 and 6, need a message alerting user.
MsgBox "The number of steps in the test must be an integer between 3 and 6.", , "Please check the number of steps in test."
TestNumBox.SetFocus
Exit Sub
Else 'Everything is filled in correctly. Exit if statment and unload the BasicData form.
End If
[\code]

As far as I know my if statment says if the the testnumbox (i.e. the textbox value is not equal to 3, or not equal to 4 or not equal to 5 or not equal to 6 then do the message box otherwise continue. 

But it always goes into the message box routine and never exits the if.  

Any ideas?

Thanks Hayden

 

[Richard Schollar]

Hi Hayden

When you test inequalities you can't use Or (you need to use AND).

Perhaps you could use:

If CInt(Textbox1.Value)>=3 And CInt(Textbox1.Value)<=6 Then
  myIntVariable = CInt(TextBox1.Value) 'use in rest of code
Else
  MsgBox "Value outside of permitted Range!" 'take appropriate action!
End If

 

[Scott Huish]

Perhaps:

Dim n As Double
n = CInt(TestNumBox)
If n >= 3 And n <= 6 And TestNumBox = n Then
    Unload BasicData
Else
    MsgBox "The number of steps in the test must be an integer between 3 and 6.", , "Please check the number of steps in test."
    TestNumBox.SetFocus
End If

 

[Richard Schollar]

Thank you Hotpepper - a much more sensible way of doing that

 

[hayden]

That was a good start but it just made the MyIntVariable an integer and didn't give the message box if it was not an integer. So I used this instead.

If CInt(TestNumBox.Value) - CDec(TestNumBox.Value) <> 0 Then
MsgBox "The number of steps in the test must be an integer between 3 and 6.", , "Please check the number of steps in test."
TestNumBox.SetFocus
Exit Sub
Else
End if
[\code]
Along with another if statement to check on the between 3 and 6 factor. 

Thanks again for your help.

 

[Scott Huish]

You shouldn't need another IF statement, my code checks for both, that's what this part does:

And TestNumBox = n</pre>
It's comparing n which is the converted to Integer value to what is is in the textbox, and if they don't match it gives the message.
So, if you put 5.07 in the box 5 would not match 5.07 and therefore show the message, how did it not work for you?

 

[hayden]

I brute forced it my way before I realized you had replied. I'm never really that elegant with my code... I guess that is the beauty of teaching yourself.

Thanks again for your reply.

 

[Norie]

Hayden

Why not use a different control?

Perhaps a combobox, a listbox, a spinner etc

 

[tusharm]

When you have a control like a textbox that can accept non-numeric values you need to validate the value as numeric before using any of the conversion routines. So, you have to use something like

Private Sub OK_Click()
    With Me
    If Not IsNumeric(.TextBox1.Text) Then 'error
    Else
        Dim N As Integer
        N = CInt(.TextBox1.Text)
        If N <> CDbl(.TextBox1.Value) Then 'error
        ElseIf N < 3 Or N > 6 Then 'error
        Else
            'all's OK
            End If
        End If
        End With
    End Sub

Note that I separated the integer test from the <3 Or >6 test so that one can provide more appropriate error messages to the user. It also separates what I consider a basic "low level" test from the more "business logic" test.

I brute forced it my way before I realized you had replied. I'm never really that elegant with my code... I guess that is the beauty of teaching yourself.

Thanks again for your reply.

 

[hayden]

Hayden

Why not use a different control?

Perhaps a combobox, a listbox, a spinner etc

I guess I never quite thought about it. That definitely would have made things easier.

tusharm - thanks for your explanation. I would assume that I could also use code similar to this to vailidate a date or a time if required? I would guess that IsNumeric could also be isDate?