Conditional Required Fields VBA

[jennifercorp]

I have a form that on submit button click needs to check for required fields and then present a msgbox that states each fields that still requires information. I was able to figure out how to check for required fields and present the message box with the code below, but it displays each individual message boxes one after the other instead of all in one msgbox. In addition to narrowing down to one msgbox, I would like for the required fields to be conditional. Say,

if A1=Yes and B1=No then check for these required fields
Else
if A2=No and B2=Yes then check for these required fields
Else
check for these required fields.

I hope that I've explained this well and would sincerely appreciate any thoughts that anyone might have on the best direction to go on this. Also, this is in a button click event. Thanks in advance.

Code that I am using now....

If Range("C9").Value = "" Then
MsgBox "Date is a required field.", vbOKOnly, "Required Field"
End If
If Range("C10").Value = "" Then
MsgBox "Auditor is a required field.", vbOKOnly, "Required Field"
End If

Else

....

 

[Mohammad Basem]

One way to do it is by building a string of all text to be displayed then display in one MsgBox statement.

Example:

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim sText As String

If Range("C9").Value = "" Then
   sText = IIf(sText = "", "Date is a required field.", sText & vbNewLine & "Date is a required field.")
End If

If Range("C10").Value = "" Then
   sText = IIf(sText = "", "Auditor is a required field.", sText & vbNewLine & "Auditor is a required field.")
End If

MsgBox sText, vbOKOnly, "Required Field"[/COLOR][/SIZE][/FONT]

 

[jennifercorp]

Thank you so much! Works like a charm!

I wound up combining this code with another code I found that checks to see if there are any fields missing, if so, show msg and exit sub, else continue. The additional code that I am using I found here: http://www.mrexcel.com/forum/showthread.php?540507-Checking-for-Empty-Excel-Fields-On-Closing-Saving So I wound up with:

Dim sText As String
Dim r As Range, txt As String
With Sheets("BCS Submition Form")
For Each r In .Range("C9,C10,I12,D23,H23")
If IsEmpty(r) Then
txt = txt & r.Address(0, 0) & vbLf
End If
Next
End With

'All
If Range("C9").Value = "" Then
sText = IIf(sText = "", "Date", sText & vbNewLine & "Date")
End If
If Range("C10").Value = "" Then
sText = IIf(sText = "", "Auditor", sText & vbNewLine & "Auditor")

'If Decline
If Range("I12").Value = "" And Range("I9").Value = "HAMP Decline" Or Range("I12").Value = "" And Range("I9").Value = "Non-HAMP Decline" Then
sText = IIf(sText = "", "Decline Reason", sText & vbNewLine & "Decline Reason")
End If

'If Approval
If Range("D23").Value = "" And Range("I9").Value = "HAMP Approval" Or Range("D23").Value = "" And Range("I9").Value = "Non-HAMP Approval" Then
sText = IIf(sText = "", "# of Borrowers", sText & vbNewLine & "# of Borrowers")
End If
If Range("H23").Value = "" And Range("I9").Value = "HAMP Approval" Or Range("H23").Value = "" And Range("I9").Value = "Non-HAMP Approval" Then
sText = IIf(sText = "", "# of Borrowers Confirmed By", sText & vbNewLine & "# of Borrowers Confirmed By")
End If

If Len(txt) > 0 Then
MsgBox sText, vbOKOnly, "Below Field(s) Required"
Exit Sub
End If

I can't tell you how I appreciate your help! That was quite the brain teaser for this newbie!

I have one other error on this workbook that I have spent hours trying to fix, but just can't get it figured out. If you get a chance to review the additional post and give me any feedback that you can think of, that would really make my day! Link to other post: http://www.mrexcel.com/forum/showth...opy-and-paste-selected-cells-to-another-sheet

Thanks again!

 

[Mohammad Basem]

Al-hamdullah, glad I could help.

Will look into the other thread shortly in-sha' Allah