Data Validation for multiple Text Boxes 1

[Pudsters]

I have a form with several text boxes and I'm using the following code for validation, which seems to work fine.

If txtName.Text= "" Then _
MsgBox "You must enter a name."

If txtCity.Text= "" Then _
MsgBox "You must enter a city."

...

Problem is if someone forgets to enter text in just one box, it wipes all the data out of the other boxes and they have to start all over. How do I get past this?

 

[MrKABC]

I would place validation text on each text box's ON EXIT property.

That way, when the user leaves your "Name" text box without having entered anything, they get the error and prompt to enter information.

Make sure you have a way of cancelling your form or your user will get stuck in a loop! =)

 

[firefytr]

What you posted does not clear any data. Post your entire code so we can have a better idea of the scope of your application.

-----------
Regards,
Zack Barresse

 

[Pudsters]

Sorry, Here is the code, I'm a beginner, I got this out of a vba book: Right now I commented out the validation because it doesn't work efficiently.


Private Sub cmdOK_Click()
Dim NextRow As Long
' Makes sure Data is the Active page
Sheets("Data").Activate
' Determines next empty row starting in Column A
NextRow = Application.WorksheetFunction. _
CountA(Range("A:A")) + 1
' Transfers info to proper column
Cells(NextRow, 1) = txtFName.Value
Cells(NextRow, 2) = txtLName.Value
Cells(NextRow, 3) = txtAddress.Value
Cells(NextRow, 4) = txtoCity.Value
Cells(NextRow, 5) = txtState.Value
Cells(NextRow, 6) = txtZip.Value
Cells(NextRow, 8) = txtDate.Value

' Data Validation for each field

' If txtFName.Value = "" Then _
MsgBox "You must enter a name."
' If txtLName.Value = "" Then _
MsgBox "You must enter a last name."
' If txtAddress.Value = "" Then _
MsgBox "You must enter an address."
' If txtCity.Value = "" Then _
MsgBox "You must enter a city."
' If txtState.Value = "" Then _
MsgBox "You must enter a state."
' If txtZip.Value = "" Then _
MsgBox "You must enter a zip code."
' If txtDate.Value = "" Then _
MsgBox "You must enter a date."



' Puts Focus on First Name, the first box and clears all information _
once the OK button is pressed. Also resets Warranty option to true.

txtFName.Value = ""
txtLName.Value = ""
txtAddress.Value = ""
txtCity.Value = ""
txtState.Value = ""
txtZip.Value = ""
txtDate.SetFocus

End Sub

 

[HarleyQuinn]

Once the messagebox is OK'd then you go straight onto clearing the textboxes, you'll need to force the user to enter something into the boxes before you continue.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Pudsters]

No, I don't know enough yet to figure that out.

 

[HarleyQuinn]

How about something like (please note it is untested as I've only written it in my reply, may need slight tweaking to run correctly):

Private Sub cmdOK_Click()
    Dim NextRow As Long
    Dim blnPopulated as Boolean
    
    blnPopulated = True

        If txtFName.Value = "" Then blnPopulated = False
        'Same for rest of your boxes...


        If blnPopulated = True then

        ' Makes sure Data is the Active page
    Sheets("Data").Activate
        ' Determines next empty row starting in Column A
    NextRow = Application.WorksheetFunction. _
        CountA(Range("A:A")) + 1
        ' Transfers info to proper column
        Cells(NextRow, 1) = txtFName.Value
        Cells(NextRow, 2) = txtLName.Value
        Cells(NextRow, 3) = txtAddress.Value
        Cells(NextRow, 4) = txtoCity.Value
        Cells(NextRow, 5) = txtState.Value
        Cells(NextRow, 6) = txtZip.Value
        Cells(NextRow, 8) = txtDate.Value
        
        ' Puts Focus on First Name, the first box and clears all information _
    txtFName.Value = ""
    txtLName.Value = ""
    txtAddress.Value = ""
    txtCity.Value = ""
    txtState.Value = ""
    txtZip.Value = ""
    txtDate.SetFocus
Else
msgbox "Please fill in all of the textboxes to proceed"
Exit Sub
End If       
End Sub

Something along those lines will validate the textboxes (you could modify the code to stop checking the boxes if it's found that one is empty, nut this code above is just an example) and then not process the rest of the code if any of the boxes are empty. It's probably not the best way of doing it but required the least re-working of your code.

Is this any help?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[rmikesmith]

I suggest something like the following:

If txtFName.Value = "" Then
  MsgBox "You must enter a name."
  txtFName.Value = ""
  txtFName.SetFocus
  Exit Sub
End If
If txtLName.Value = "" Then _
  MsgBox "You must enter a last name."
  txtLName.Value = ""
  txtLName.SetFocus
  Exit Sub
End If 
If txtAddress.Value = "" Then _
  MsgBox "You must enter an address."
  txtAddress.Value = ""
  txtAddress.SetFocus
  Exit Sub
End If
If txtCity.Value = "" Then _
  MsgBox "You must enter a city."
  txtCity.Value = ""
  txtCity.SetFocus
  Exit Sub
End If
If txtState.Value = "" Then _
  MsgBox "You must enter a state."
  txtState.Value = ""
  txtState.SetFocus
  Exit Sub
End If
If txtZip.Value = "" Then _
  MsgBox "You must enter a zip code."
  txtZip.Value = ""
  txtZip.SetFocus
  Exit Sub
End If
If txtDate.Value = "" Then _
  MsgBox "You must enter a date."
  txtDate.Value = ""
  txtDate.SetFocus
  Exit Sub
End If

Eliminate the lines that clear all the textboxes (at the end of your code).

Personally, I prefer to have the code check all inputs and create a string indicating which are not valid, then present this at once to the user. Code would set the focus to the first invalid TextBox control.


Regards,
Mike

 

[Pudsters]

HarlyQuinn, that worked perfect and was quite easy to understand and implement. Thank you very much!

Mike, I just saw your code, thank you as well, I will check it out.

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.