Page 1 of 2 12 LastLast
Results 1 to 10 of 12

VBA Not Equal Statement

This is a discussion on VBA Not Equal Statement within the Excel Questions forums, part of the Question Forums category; Hello, I'm trying to write a vba statement to check that the user has entered an integer in my userform ...

  1. #1
    Board Regular
    Join Date
    Sep 2005
    Location
    Denver Colorado
    Posts
    156

    Default VBA Not Equal Statement

    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

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: VBA Not Equal Statement

    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
    Richard Schollar

    Using xl2013

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,051

    Default Re: VBA Not Equal Statement

    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
    Office 2007/2010

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: VBA Not Equal Statement

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

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Sep 2005
    Location
    Denver Colorado
    Posts
    156

    Default Re: VBA Not Equal Statement

    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.

  6. #6
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,051

    Default Re: VBA Not Equal Statement

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

    And TestNumBox = n
    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?
    Office 2007/2010

  7. #7
    Board Regular
    Join Date
    Sep 2005
    Location
    Denver Colorado
    Posts
    156

    Default Re: VBA Not Equal Statement

    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.

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: VBA Not Equal Statement

    Hayden

    Why not use a different control?

    Perhaps a combobox, a listbox, a spinner etc
    If posting code please use code tags.

  9. #9
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default Re: VBA Not Equal Statement

    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.

    Quote Originally Posted by hayden View Post
    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.

  10. #10
    Board Regular
    Join Date
    Sep 2005
    Location
    Denver Colorado
    Posts
    156

    Default Re: VBA Not Equal Statement

    Quote Originally Posted by Norie View Post
    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?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com