Dear Team,
I'm new to VBA and i'm trying to develop a data entry userform for my lab. In the userform, upon selecting the product the specifications will get automatically populated. The given specifications are range of values separated by hyphen. I will split the range and store in 2 reference textboxes named split1 and split2. Now i have results which will be entered in Textboxes named res1, res2 ..... res15. Once the result is entered, it will get compared to split1 and split2 and if it is out of the given specification range it will show in red. The problem i'm facing is when i enter the result values the validation happens randomly for some values.
Example: If i select the product as Caustic_Soda_Lye_50..... For Silicate the range is 0 - 10, it works only when the result is given as 10. Please help me to solve this issue.
I'm new to VBA and i'm trying to develop a data entry userform for my lab. In the userform, upon selecting the product the specifications will get automatically populated. The given specifications are range of values separated by hyphen. I will split the range and store in 2 reference textboxes named split1 and split2. Now i have results which will be entered in Textboxes named res1, res2 ..... res15. Once the result is entered, it will get compared to split1 and split2 and if it is out of the given specification range it will show in red. The problem i'm facing is when i enter the result values the validation happens randomly for some values.
Example: If i select the product as Caustic_Soda_Lye_50..... For Silicate the range is 0 - 10, it works only when the result is given as 10. Please help me to solve this issue.
VBA Code:
Function ValidateEntries() As Boolean
ValidateEntries = True
Dim iCertid As Variant
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CoA")
iCertid = frmForm.txtcert.Value
With frmForm
If Trim(.txtname.Value) = "" Then
MsgBox "Please Enter Company Name", vbOKOnly + vbInformation, "Name"
ValidateEntries = False
.txtname.BackColor = vbRed
.txtname.SetFocus
Exit Function
End If
If Trim(.cmbzone.Value) = "" Then
MsgBox "Please Select Product", vbOKOnly + vbInformation, "Zone"
ValidateEntries = False
.cmbzone.BackColor = vbRed
.cmbzone.SetFocus
Exit Function
End If
'Validating Duplicate Entries--------------------------------
If Not sh.Range("B:B").Find(what:=iCertid, lookat:=xlWhole) Is Nothing Then
MsgBox "Duplicate Certificate Number Found", vbOKOnly + vbInformation, "Certificate"
ValidateEntries = False
.txtcert.BackColor = vbRed
.txtcert.SetFocus
Exit Function
End If
'----------------------------------------------------------------------
'Validating Specifications--------------------------------
If .txtsp1.Value Like "*[A-Z]*" Then
.txtres1.Value = .txtsp1.Value
ElseIf .txtsp1.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp1.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp1.Value, " - ")(1))
If .txtres1.Value < .split1.Value Or .txtres1.Value > .split2.Value Then
ValidateEntries = False
.txtres1.BackColor = vbRed
.txtres1.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp2.Value Like "*[A-Z]*" Then
.txtres2.Value = .txtsp2.Value
ElseIf .txtsp2.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp2.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp2.Value, " - ")(1))
If .txtres2.Value < .split1.Value Or .txtres2.Value > .split2.Value Then
ValidateEntries = False
.txtres2.BackColor = vbRed
.txtres2.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp3.Value Like "*[A-Z]*" Then
.txtres3.Value = .txtsp3.Value
ElseIf .txtsp3.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp3.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp3.Value, " - ")(1))
If .txtres3.Value < .split1.Value Or .txtres3.Value > .split2.Value Then
ValidateEntries = False
.txtres3.BackColor = vbRed
.txtres3.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp4.Value Like "*[A-Z]*" Then
.txtres4.Value = .txtsp4.Value
ElseIf .txtsp4.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp4.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp4.Value, " - ")(1))
If .txtres4.Value < .split1.Value Or .txtres4.Value > .split2.Value Then
ValidateEntries = False
.txtres4.BackColor = vbRed
.txtres4.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp5.Value Like "*[A-Z]*" Then
.txtres5.Value = .txtsp5.Value
ElseIf .txtsp5.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp5.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp5.Value, " - ")(1))
If .txtres5.Value < .split1.Value Or .txtres5.Value > .split2.Value Then
ValidateEntries = False
.txtres5.BackColor = vbRed
.txtres5.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp6.Value Like "*[A-Z]*" Then
.txtres6.Value = .txtsp6.Value
ElseIf .txtsp6.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp6.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp6.Value, " - ")(1))
If .txtres6.Value < .split1.Value Or .txtres6.Value > .split2.Value Then
ValidateEntries = False
.txtres6.BackColor = vbRed
.txtres6.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp7.Value Like "*[A-Z]*" Then
.txtres7.Value = .txtsp7.Value
ElseIf .txtsp7.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp7.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp7.Value, " - ")(1))
If .txtres7.Value < .split1.Value Or .txtres7.Value > .split2.Value Then
ValidateEntries = False
.txtres7.BackColor = vbRed
.txtres7.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp8.Value Like "*[A-Z]*" Then
.txtres8.Value = .txtsp8.Value
ElseIf .txtsp8.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp8.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp8.Value, " - ")(1))
If .txtres8.Value < .split1.Value Or .txtres8.Value > .split2.Value Then
ValidateEntries = False
.txtres8.BackColor = vbRed
.txtres8.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp9.Value Like "*[A-Z]*" Then
.txtres9.Value = .txtsp9.Value
ElseIf .txtsp9.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp9.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp9.Value, " - ")(1))
If .txtres9.Value < .split1.Value Or .txtres9.Value > .split2.Value Then
ValidateEntries = False
.txtres9.BackColor = vbRed
.txtres9.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp10.Value Like "*[A-Z]*" Then
.txtres10.Value = .txtsp10.Value
ElseIf .txtsp10.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp10.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp10.Value, " - ")(1))
If .txtres10.Value < .split1.Value Or .txtres10.Value > .split2.Value Then
ValidateEntries = False
.txtres10.BackColor = vbRed
.txtres10.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp11.Value Like "*[A-Z]*" Then
.txtres11.Value = .txtsp11.Value
ElseIf .txtsp11.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp11.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp11.Value, " - ")(1))
If .txtres11.Value < .split1.Value Or .txtres11.Value > .split2.Value Then
ValidateEntries = False
.txtres11.BackColor = vbRed
.txtres11.SetFocus
Exit Function
End If
'Validating Specifications--------------------------------
If .txtsp12.Value Like "*[A-Z]*" Then
.txtres12.Value = .txtsp12.Value
ElseIf .txtsp12.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp12.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp12.Value, " - ")(1))
If .txtres12.Value < .split1.Value Or .txtres12.Value > .split2.Value Then
ValidateEntries = False
.txtres12.BackColor = vbRed
.txtres12.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp13.Value Like "*[A-Z]*" Then
.txtres13.Value = .txtsp13.Value
ElseIf .txtsp13.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp13.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp13.Value, " - ")(1))
If .txtres13.Value < .split1.Value Or .txtres13.Value > .split2.Value Then
ValidateEntries = False
.txtres13.BackColor = vbRed
.txtres13.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp14.Value Like "*[A-Z]*" Then
.txtres14.Value = .txtsp14.Value
ElseIf .txtsp14.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp14.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp14.Value, " - ")(1))
If .txtres14.Value < .split1.Value Or .txtres14.Value > .split2.Value Then
ValidateEntries = False
.txtres14.BackColor = vbRed
.txtres14.SetFocus
Exit Function
End If
End If
'Validating Specifications--------------------------------
If .txtsp15.Value Like "*[A-Z]*" Then
.txtres15.Value = .txtsp15.Value
ElseIf .txtsp15.Value = "" Then
Exit Function
Else
.split1.Value = Trim(Split(.txtsp15.Value, " - ")(0))
.split2.Value = Trim(Split(.txtsp15.Value, " - ")(1))
If .txtres15.Value < .split1.Value Or .txtres15.Value > .split2.Value Then
ValidateEntries = False
.txtres15.BackColor = vbRed
.txtres15.SetFocus
Exit Function
End If
End If
End If
End With
End Function
Last edited by a moderator: