Vba Userform data validation

[Skuli]

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 Combobox 3 had an entry, then one of Textbox 3 and 4 would need an entry and so would 5. If Combobox 3 had one then Combobox 4, Textbox 6 and 7 would not. If Combobox 4 had an entry then one of Textbox 6 and 7 would need one. If Combobox 4 had one then I would need no entry in Combobox 3 Textbox 3, 4 and 5.
Here is the code 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

This is well beyond me I hope someone can help

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

I don't understand your description of the data validation rules. Please clarify andnexplain your overall aim rather than describing the code you currently have.

[Skuli]

Here it is

[Skuli]

Ok If I have done this right, the first screen shot should be one of my workbook. I have made a slight mistake on this make the example to show.
Column G heading should be Cat 1 units, Column H should be Cat 1 weight, Column I should be Cat 1 value.

On the left you can see an add new record button. When clicked a user form opens. Screen shot 2.
What I’m trying to do and failing at is that if someone clicks add record and save button, it will only let them if some of the boxes have been filled out.
Screen shot 3 shows this working and it does for the first three fields.

What I can’t get it to do is the fourth field Cat 1 (ComboBox 3 in the code). If data is entered here then I will need data in
field five cat 1 units (TextBox 3 in the code) or six Cat 1 weight (Textbox 4 in the code), but not both and always 7 Cat 1 Value (Textbox 5 in the code).
If they use the fourth field then I would not want them to use the eighth, nineth or tenth
If they use field eight Cat 2 (ComboBox4 in the code) then I would need data in fields nine Cat 2 Mil/Lit (Textbox 6 in the code) and ten Cat 2 value
(Textbox 7 in the code). If they used field nine, I would not want them to enter any date in fourth, fifth, sixth and seventh fields.

[torachan]

try the attached it should only allow either Cat1 or Cat2 data to be entered and should check if data is missing.

[Skuli]

try the attached it should only allow either Cat1 or Cat2 data to be entered and should check if data is missing.

Thank you this works brilliantly. I don’t understand any of it but it just what I wanted.

Again thank you for doing this.

[torachan]

thanks for the feedback and rep point.
when i get 5 minutes in the next couple of days i will post an annotated pdf of the code to illustrate the structure.

[torachan]

as promised annotated code - hope this assists understanding