Results 1 to 4 of 4

Thread: Data Validation

  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

    Help Please, I need assistance with two things. The following are lines of code cut from the [Done] button on a UserForm. In the first example, a number is supposed to be entered in the dollars and cents format (0.00). It would be acceptible if the user just entered a whole number (4 would be understood as 4.00). But the validation code I am using would allow the user to enter 4.98765097 which is obviously ungood!

    In the second example, the user should only be allowed to enter a whole number from 0 to 59 as this represents seconds. Again, with the validation I have in place, the user could enter 1.8964712577 and that too, is ungood.

    This code forces the user to enter a number but it needs to do more...

    If IsNumeric(BoxLabor) = False Then
    MsgBox "Invalid Entry, Try Again!", , "Labor"
    BoxLabor.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, Try Again!", , "Service"
    BoxReply.SetFocus
    Exit Sub
    End If

    Can someone please lead me in the proper direction?
    - 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

    Ricky,

    There are many different ways to validate the data you enter. Here is a post that might help you: Click here

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation

    Maybe something like this might help:

    <pre> If InStr(BoxLabor, ".") > 0 Then
    If Len(BoxLabor) - InStr(BoxLabor, ".") > 2 Then
    MsgBox "More than two digits after decimal"
    End If
    End If
    </pre>

    Legare Coleman

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation

    Legare, that's nice.

    Ricky, something you might experiment with is to put this code in the KeyPress event of the box. It would stop your users even trying to enter charatcers:

    <pre>Private Sub boxlabor_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If InStr(boxlabor, ".") > 0 Then
    If Len(boxlabor) - InStr(boxlabor, ".") >= 2 Then
    KeyAscii = 0
    End If
    End If

    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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