Conditional Required Fields VBA

jennifercorp

New Member
Joined
Jun 25, 2012
Messages
11
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

....
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
One way to do it is by building a string of all text to be displayed then display in one MsgBox statement.

Example:
Code:
[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]
 
Upvote 0
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:

Code:
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! :rolleyes:

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!
 
Upvote 0

Forum statistics

Threads
1,211,454
Messages
6,101,944
Members
447,764
Latest member
gopalgriffith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top