Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Data Validation, Again (XL2000 9.0.3821 SR-1)

    The following lines do not work properly and do not accomplish what I need. On a UserForm, the user is entering the number of seconds it takes to reply to a customer. The first IF makes sure the reply is a number and the second IF makes sure the reply is a number betweek 0 and 59. The problem is it allows the user to enter a decimal number. Only a whole number without a decimal should be permitted. Again, any advice would be appreciated.

    Private Sub FinishBtn_Click()

    'Validate data entries on userform prior to writing to databases

    If IsNumeric(BoxReply) = False Then
    MsgBox "Reply Time must be entered as a number.", , "Service"
    BoxReply.SetFocus
    Exit Sub
    End If

    If Val(BoxReply) < 0 Or Val(BoxReply) > 59 Then
    MsgBox "Invalid Entry, Reply Time must be entered as a whole number from 0 - 59.", , "Service"
    BoxReply.SetFocus
    Exit Sub
    End If
    - Ricky

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation, Again (XL2000 9.0.3821 SR-1)

    Maybe you can use the VarType VBA function; e.g.

    if Val(BoxReply)<0 or Val(BoxReply)>59 or VarType(Val(BoxReply))<>vbInteger then

    I can't test it out right now, because my son made office crashing and I have to reinstall it, but I am quite sure that the VarType function will help you here. Have a look in the Help for the different data types.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation, Again (XL2000 9.0.3821 SR-1)

    Thanks for responding - I tried adding what you suggested: xlinteger and vbinteger but neither of them would allow me to enter a correct response into the userform. It would not allow the whole number entries from 0 to 59.
    - Ricky

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation, Again (XL2000 9.0.3821 SR-1)

    Sorry Ricky, I thought you've read the boxreply from a cell on the spreadsheet (in that case, vartype should work). If you enter data in a textbox on a userform and you want it to be an integer, you can use something like this (I just slightly changed your code; the essential thing is the added command int(Ans)<>Ans. If the integer part of the BoxReply value is different from the BoxReply value itself, then it is definitely no integer.

    <pre>Private Sub CommandButton1_Click()
    Dim Ans As Double
    Ans = Val(BoxReply)

    If IsNumeric(BoxReply) = False Then
    MsgBox "Reply Time must be entered as a number.", , "Service"
    BoxReply.SetFocus
    Exit Sub
    End If

    If Ans < 0 Or Ans > 59 Or (Int(Ans) <> Ans) Then
    MsgBox "Invalid Entry, Reply Time must be entered as a whole _
    number from 0 - 59.", , "Service"
    BoxReply.SetFocus
    Exit Sub
    End If

    End Sub
    </pre>


  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation, Again (XL2000 9.0.3821 SR-1)

    Hans - I appreciate the help. I used the code that you suggested and it works perfectly allowing the user to enter only whole numbers between 0 and 59. Just right!
    - Ricky

Posting Permissions

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