Hi All,
I have a worksheet which is used as a questionnaire. I have been trying to have an error check on mandatory fields that must be filled out by the user. This part has worked out fine - mostly. But the challenge is with connected fields that are dependent on the response from the user in the first set of mandatory fields. Do note that all the fields in the code are mandatory. I seem to be going in circles trying to find a code that would work in all scenarios.
So to explain this if fields c23 and c24 - (group a) are empty, then data must be pulled from, C25, C27:C29 (group b). Sounds simple, right. when I try to cover all scenarios of whether data in either of these groups is empty or partially empty then the code hits an error. I think a loop to check on which set of fields must be checked depending on either group a or group b have data in all fields, but I am unable to work it out. I seem to also get lost in combining multiple ranges via the For loop. And perhaps there's a smarter way of doing this.
Thank you for your help in advance!
I have a worksheet which is used as a questionnaire. I have been trying to have an error check on mandatory fields that must be filled out by the user. This part has worked out fine - mostly. But the challenge is with connected fields that are dependent on the response from the user in the first set of mandatory fields. Do note that all the fields in the code are mandatory. I seem to be going in circles trying to find a code that would work in all scenarios.
So to explain this if fields c23 and c24 - (group a) are empty, then data must be pulled from, C25, C27:C29 (group b). Sounds simple, right. when I try to cover all scenarios of whether data in either of these groups is empty or partially empty then the code hits an error. I think a loop to check on which set of fields must be checked depending on either group a or group b have data in all fields, but I am unable to work it out. I seem to also get lost in combining multiple ranges via the For loop. And perhaps there's a smarter way of doing this.
Thank you for your help in advance!
VBA Code:
Sub startform()
If ThisWorkbook.ConnectionError = False Then
UserForm2.Show
Dim R1, R2, R3, R4, R5, R6, R7, Multirng, myRange, rng As Range
Dim cell As Range
Dim myString As String
Dim ws As Worksheet
Set ws = Sheets("Employee")
If ws.Range("C3").Value = "" Or _
ws.Range("C4").Value = "" Or _
ws.Range("C5").Value = "" Or _
ws.Range("C6").Value = "Select" Or _
ws.Range("C8").Value = "Select" Or _
ws.Range("C11").Value = "Select" Or _
ws.Range("C13").Value = "" Or _
ws.Range("C14").Value = "" Or _
ws.Range("C21").Value = "" Or _
ws.Range("C22").Value = "" Or _
ws.Range("C34").Value = "Select" Or _
ws.Range("C42").Value = "Select" Or _
ws.Range("C43").Value = "" Or _
ws.Range("C44").Value = "" Or _
ws.Range("C45").Value = "" Or _
ws.Range("C46").Value = "" Then
Set Multirng = Range("C3:C6, C8, C11, C13:C14, C21:C22, C34, C42:C46")
If ws.Range("C23").Value <> "" And ws.Range("C24").Value <> "Select" And ws.Range("C25, C27:C29").Value = "" Then
Set rng = ws.Range("C23:C24")
Set myRange = Union(Multirng, rng)
ElseIf ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" And ws.Range("C25, C27:C29").Value = "" Then
Set rng = ws.Range("C23:C24")
Set myRange = Union(Multirng, rng)
ElseIf ws.Range("C25, C27:C29").Value = "" Then
If ws.Range("C23").Value <> "" And ws.Range("C24").Value <> "Select" Or _
ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" Or _
ws.Range("C23").Value <> "" And ws.Range("C24").Value = "Select" Or _
ws.Range("C23").Value = "" And ws.Range("C24").Value <> "Select" Then
Set rng = ws.Range("C23:C24")
Set myRange = Union(Multirng, rng)
End If
ElseIf ws.Range("C23").Value = "" And ws.Range("C24").Value = "Select" And ws.Range("C25, C27:C29").Value <> "" Then
Set rng = ws.Range("C25, 27:C29")
End If
Else
'**Continue to the rest of the code"
End If
'***Loop for listing empty fields in the questionnaire and their values
For Each cell In myRange
If cell = "" Or cell = "Select" Then
myString = myString & cell.Address(0, 0) & ": " & cell.Offset(0, -1).Value & "," & vbCrLf
End If
Next cell
If Len(myString) = 0 Then
Call AnsigterSQL
UserForm1.Show
Else
MsgBox "Please complete these empty fields and try again:" & vbCrLf & vbCrLf & Left(myString, Len(myString) - 1), vbInformation, "Mandatory Cells Not Completed"
End If
End If
End Sub