VBA Not Equal Statement

H

hayden

Board Regular
Joined
Sep 23, 2005
Messages
188
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:

Code: 
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hi Hayden

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

Perhaps you could use:

Code: 
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
 
Upvote 0
Perhaps:

Code: 
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
 
Upvote 0
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.
Code: 
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Hayden

Why not use a different control?

Perhaps a combobox, a listbox, a spinner etc
 
Upvote 0
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
Code: 
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.

hayden said:
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.
Click to expand...
 
Upvote 0
Norie said:
Hayden

Why not use a different control?

Perhaps a combobox, a listbox, a spinner etc
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help Needed
2
Replies
13
Views
319
kwavic
K
Z
  • Solved
Excel VBA: Using OR with <>
Replies
3
Views
365
rlv01
R
B
  • Question Question
Simulator for one event in blackjack game
Replies
2
Views
411
baha17
B
C
  • Question Question
Force user to enter at least one symbol and one number in a textbox through a userform
Replies
9
Views
305
christianbiker
C
Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
625
zero269
Z

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top