Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation on Text Boxes in VB Forms (2003)

    This is kind of my first real attempt at making a nice form for a user to enter data. There are a few remaining things I would like to do and could use some help/suggestions.

    1. DATA FORMAT/VALIDATION: I have one text box that asks for a dollar amount. Is there a way to format this field to a $##0.00 type format when the user clicks/tabs out of the field? I tried using the _Change() and _LostFocus() but no luck there. I also have a text box that asks for a date. Is there a way to format this also when the user clicks/tabs out of the field? Also, should I be concerned with data validation (i.e. a number must be entered for dollar amount and that a date must be entered for the date)?

    2. BLANK TEXT BOXES: When is the best time to check that all required data has been entered? Should this happen when the user clicks OK or when they click/tab out of a particular required field? Also, with several required fields, I can't quite think of the best way to code this to check that there are not blank fields still. Any suggestions?

    I think I covered it all. Thanks!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation on Text Boxes in VB Forms (2003)

    If you really want to do this, you should use Access instead of Excel. Access text boxes have features such as a Format property, an Input Mask, automatic checking for correct numbers/dates, and many more.

    If you must stay with Excel, I wouldn't bother with imposing a format (others may disagree with me).
    I'd put all error checking in the On Click event procedure of the OK button. Simply check each control in turn, and if it doesn't meet the requirements, set focus to the control, display a message and exit the event procedure.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation on Text Boxes in VB Forms (2003)

    Thanks Hans!

    Unforunately I have to complete this project in Excel and cannot use Access.

    Is there a more efficient way to do the error checking (see code below) in the On Click event procedure for the OK button?

    Example:

    Sub ErrorCheck ()
    If TextBox1.Value = "" Then
    MsgBox "You must enter a First Name."
    Exit Sub
    End If
    If TextBox2.Value = "" Then
    MsgBox "You must enter a Last Name."
    Exit Sub
    End If
    If TextBox3.Value = "" Then
    MsgBox "You must enter a Date."
    Exit Sub
    End If
    Call OkButtonClickFunction () 'Calls function if OK button is clicked and criteria met
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation on Text Boxes in VB Forms (2003)

    That's the way to do it. I would add lines to set focus to the offending control:

    If Me.TextBox1.Value = "" Then
    Me.TextBox1.SetFocus
    MsgBox "You must enter a First Name."
    Exit Sub
    End If

    etc.

  5. #5
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation on Text Boxes in VB Forms (2003)

    Ah yes. Good point!

    Thank you for your help!!

  6. #6
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation on Text Boxes in VB Forms (2003)

    Just to update the post if anyone is interested in a possible solution to the DATA FORMAT/VALIDATION part. When the user either clicks or tabs out of the Date field the code will first do a validation check to make sure it is a date and then format the cell to mm/dd/yyyy. The code will also ignore blank entries

    Private Sub DateTextBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsDate(DateTextBox.Value) And Not DateTextBox.Value = "" Then
    Cancel = True
    DateTextBox.Value = ""
    DateTextBox.SetFocus
    MsgBox "Please enter a Date value."
    End If
    DateTextBox.Value = Format(DateTextBox.Value, "mm/dd/yyyy")
    End Sub

Posting Permissions

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