Vba data entry form, validation help

[Mat J]

Hello, I’m after some help. I have made a vba user form which is working ok. I want to add some data validation to it. I have managed to add a little. Example would be Me.textbox1.Value = “” Then

MsgBox "Please enter date", vbCritical

Exit Sub

End If



This works fine for most boxes the issue I have is that not all my textboxes need an entry, some will if others have one. So an example. If textbox 2 had an entry, then one of 3 and 4 would need an entry and so would 5. If textbox 2 had one then 6, 7, 8 and 9 would not. If textbox 6 had an entry then one of 7 and 8 would need one, so would 9. If textbox 6 had one then I would need no entry in 2, 3, 4 and 5.



This is well beyond me I hope someone can help

 

[6StringJazzer]

When you have those kinds of cross-dependencies, you don't know there is a problem until the user tells you that they're done entering data. So you can't do the validation until the user is done. Do you have a Submit button?

This is the general idea. You will have to develop the logic specific to your situation. You would use the actual data descriptions in the messages, not the control names (i.e., "If you provide a street, you must provide city and state")

Private Sub ButtonSubmit_Click()

   Dim Msg As String

   If textbox2 <> "" Then
      If textbox3 = "" and textbox4 = "" Then
         Msg = Msg & "If textbox2 has data, you must fill in either textbox3 or textbox4"
      End If
      If textbox5 = "" Then 
         Msg = Msg & "If textbox2 has data, you must fill in textbox5"
      End If
   End If

' More validation here

   If Msg = "" Then
      ' Do Submit logic here
      Me.Hide
   Else
      MsgBox Msg
   End If

End Sub

 

[Mat J]

When you have those kinds of cross-dependencies, you don't know there is a problem until the user tells you that they're done entering data. So you can't do the validation until the user is done. Do you have a Submit button?

This is the general idea. You will have to develop the logic specific to your situation. You would use the actual data descriptions in the messages, not the control names (i.e., "If you provide a street, you must provide city and state")

Private Sub ButtonSubmit_Click()

   Dim Msg As String

   If textbox2 <> "" Then
      If textbox3 = "" and textbox4 = "" Then
         Msg = Msg & "If textbox2 has data, you must fill in either textbox3 or textbox4"
      End If
      If textbox5 = "" Then
         Msg = Msg & "If textbox2 has data, you must fill in textbox5"
      End If
   End If

' More validation here

   If Msg = "" Then
      ' Do Submit logic here
      Me.Hide
   Else
      MsgBox Msg
   End If

End Sub

Hello and thank you for the reply. Yes the user form has a submit button, I will give this a look at tomorrow and post more of the code. Again thank you for taking the time to have a look.

 

[Mat J]

Here is the vba I'm using

Private Sub CommandButton2_Click()

 
    If Me.TextBox1.Value = "" Then
    MsgBox "Please enter the date", vbCritical
    Exit Sub
   End If
   
    If Me.ComboBox1.Value = "" Then
    MsgBox "Please enter the team number", vbCritical
    Exit Sub
   End If
   
    If Me.ComboBox2.Value = "" Then
    MsgBox "Please enter the shift", vbCritical
    Exit Sub
   End If
   
   Dim TargetRow As Integer
   

TargetRow = Sheets("Engine").Range("C6").Value + 1
Refrence = "Record number " & TargetRow 'End msgbox'

Sheets("Input database").Range("Data_Start").Offset(TargetRow, 0).Value = TargetRow
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 1).Value = TextBox1
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 2).Value = ComboBox1
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 3).Value = "Shop 1"
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 4).Value = ComboBox2
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 5).Value = ComboBox3
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 6).Value = TextBox3
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 7).Value = TextBox4
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 8).Value = TextBox5
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 9).Value = ComboBox4
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 10).Value = TextBox6
Sheets("Input database").Range("Data_Start").Offset(TargetRow, 11).Value = TextBox7

MsgBox "For any dates, did you use number and text? If you have not please edit and do so.", vbInformation
MsgBox Refrence & " was added to the shop 1 Input databse", 0, "Complete"



Unload Add_Record
    
End Sub

 

[Mat J]

So if they use combobox 3 I want them to use either textbox 3 or 4 and 5. I would then not need them to use any after. If they used combo box 4 I need them to use textbox 6 and 7, but not combobox 3, textbox 3, 4 and 5.