Solved Simplifying a long if statement in VBA

July 31, 2018 at 04:41:43
Specs: Windows 7
I have a userform that I have some required fields, after some searching I used the following code that works well but I was wondering if there was a shorter way of writing the code:
If TextBoxB1Income.Value = "" Then
    MsgBox "You must enter Borrower 1's income", vbCritical
    Exit Sub
End If
If TextBoxSalesPrice.Value = "" Then
    MsgBox "You must enter a Sales Price", vbCritical
    Exit Sub
End If
If TextBoxLTV.Value = "" Then
    MsgBox "You must enter an LTV", vbCritical
    Exit Sub
End If
If TextBoxTaxes.Value = "" Then
    MsgBox "You must enter annual property taxes", vbCritical
    Exit Sub
End If
If TextBoxInsurance.Value = "" Then
    MsgBox "You must enter annual insurance premium", vbCritical
    Exit Sub
End If
If TextBoxLoanType = "" Then
    MsgBox "You must enter select loan type", vbCritical
    Exit Sub
End If
If TextBoxRate.Value = "" Then
    MsgBox "You must enter an interest rate", vbCritical
    Exit Sub
End If
If TextBoxMaxHousing.Value = "" Then
    MsgBox "You must enter a Maximum DTI", vbCritical
    Exit Sub
End If
If TextBoxMaxDTI.Value = "" Then
    MsgBox "You must enter a Maximum Housing Ratio", vbCritical
    Exit Sub
End If
If TextBoxMI.Value = "" And TextBoxLTV.Value > 80 Then
    MsgBox "You must enter an MI factor when LTV is greaten than 80%", vbCritical
    Exit Sub
End If


See More: Simplifying a long if statement in VBA

Reply ↓  Report •

#1
July 31, 2018 at 05:01:56
✔ Best Answer
Something like this will help, this will look through all the controls on your userform, then check only the textboxes for empty values. If any textbox is empty it will ask you to complete all fields.

The only issue would be that it will not give you specific error messages per textbox, it can be done though..

Dim Obj As Control

Private Sub CommandButton1_Click()
    
    For Each Obj In UserForm1.Controls
        
        If TypeOf Obj Is MSForms.TextBox Then
            
            If Obj.Value = vbNullString Then
                MsgBox "Hey please complete all fields"
                Exit For
            End If
            
        End If
        
    Next Obj

End Sub


Reply ↓  Report •

#2
August 1, 2018 at 17:22:58
Thank you, it worked :-)

Reply ↓  Report •

#3
August 2, 2018 at 00:26:05
Awesome glad it worked for you and thanks for coming back to tell us :)

Reply ↓  Report •
Related Solutions


Ask Question