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

    Revisitng Data Validation (XL2000 9.0.3821 SR-1)

    The following lines work perfectly and accomplish just what I need but I'm sure these two routines could be combined to accomplish the same result. On a UserForm, the user is entering Happy Hour sales. The first IF makes sure the reply is a number and the second IF makes sure the reply only has two digits following the decimal. Any advice would be appreciated as I am having to validate dozens of entries!

    Private Sub FinishBtn_Click()

    'Validate data entries on userform prior to writing to databases

    If IsNumeric(Box_HH) = False Then
    MsgBox "Happy Hour Sales must be a number.", , "Sales"
    Box_HH.SetFocus
    Exit Sub
    End If

    If Len(Box_HH) - InStr(Box_HH, ".") > 2 Then
    MsgBox "More than 2 digits after the decimal.", , "Sales"
    Box_HH.SetFocus
    Exit Sub
    End If
    ...
    - Ricky

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

    Re: Revisitng Data Validation (XL2000 9.0.3821 SR-1)

    Ricky,

    Put your validation into a routine which you can call from anywhere. Then refine it, generalise, add paramters, and you will have a routine you can use anywhere.

    First step:<pre>Private Sub UserForm_Click()
    If Not ValidateN2Message(Box_HH) Then
    Exit Sub
    End If
    MsgBox "Value OK"
    End Sub
    Private Function ValidateN2Message(ctlFocus As Control) As Boolean
    'Validate data entries on userform prior to writing to databases
    ValidateN2Message = False
    If IsNumeric(ctlFocus.Text) = False Then
    MsgBox "Happy Hour Sales must be a number.", , "Sales"
    ctlFocus.SetFocus
    Exit Function
    End If

    If Len(ctlFocus.Text) - InStr(ctlFocus.Text, ".") > 2 Then
    MsgBox "More than 2 digits after the decimal.", , "Sales"
    ctlFocus.SetFocus
    Exit Function
    End If

    ValidateN2Message = True
    End Function</pre>

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

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

    Re: Revisitng Data Validation (XL2000 9.0.3821 SR-1)

    Geoff -

    Thanks so much - That's going to be a real time-saver and will surely eliminate typing errors as I run each value through the same validation. Thanks again.
    - 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
  •