CODEWITHSUNDEEP
excelvba
methuselah
methuselah

Reputation: 13206

If-then statement in VBA

I'm currently writing two sets of if-else statements which are very similar. They basically compare three drop down menus and ensure that the user has not put down two matching preferences. For example:

cbo_fac1 - Kitchen    
cbo_fac2 - Kitchen   
cbo_fac3 - Lounge

This would return an error message because cbo_fac1 and cbo_fac2 match up. But there is a special case scenario I'm struggling to implement. One of the drop down cases is No preference.

cbo_fac1 - Kitchen    
cbo_fac2 - No preference
cbo_fac3 - No preference

cbo_fac1 - No preference    
cbo_fac2 - No preference
cbo_fac3 - No preference

With any scenario No preference selection is allowed to match. How do I go about implementing this? Here is the code I'm using so far:

If cbo_fac1.Value = cbo_fac2.Value Then
    MsgBox ("Facilities preference 1 and facilities preference 2 cannot be the same. Please select another option for facilities preference 2, if you have none then select 'No preference'")
    Exit Sub
End If

If cbo_fac1.Value = cbo_fac3.Value Then
    MsgBox ("Facilities preference 1 and facilities preference 3 cannot be the same. Please select another option for facilities preference 3, if you have none then select 'No preference'")
    Exit Sub
End If

If cbo_fac2.Value = cbo_fac3.Value Then
    MsgBox ("Facilities preference 2 and facilities preference 3 cannot be the same. Please select another option for facilities preference 3, if you have none then select 'No preference'")
    Exit Sub
End If

Upvotes: 1

Views: 10752

Answers (2)

Daniel
Daniel

Reputation: 13122

If you want to write it as one giant if statement, this should do:

If (cbo_fac1.Value <> cbo_fac2.Value Or cbo_fac1.Value = "No Preference") And _
   (cbo_fac2.Value <> cbo_fac3.Value Or cbo_fac2.Value = "No Preference") And _
   (cbo_fac1.Value <> cbo_fac3.Value Or cbo_fac3.Value = "No Preference") Then
     'Input is fine
else
     exit sub

End If

Edit:

Just because here's the reverse way, with a possible msgbox:

If (cbo_fac1.value = cbo_fac2.value AND cbo_fac1.value <> "No Preference") OR _
   (cbo_fac2.value = cbo_fac3.value AND cbo_fac2.value <> "No Preference") OR _
   (cbo_fac1.value = cbo_fac3.value AND cbo_fac3.value <> "No Preference") then

   Msgbox "No two facilities can be the same. Please select another option " & _
          "for facilities preference, if you have none then select 'No preference'"
   exit sub
else
   'input is fine
end if

Upvotes: 3

Siva Charan
Siva Charan

Reputation: 18064

Check something like

If cbo_fac1.Value = cbo_fac2.Value and cbo_fac1.Value <> "No Preference" and cbo_fac2.Value <> "No Preference" Then
    MsgBox ("Facilities preference 1 and facilities preference 2 cannot be the same. Please select another option for facilities preference 2, if you have none then select 'No preference'")
    Exit Sub
End If

Do the same check for other 2 IF conditions.

UPDATE:

There is another way, by writing a function

Below is the sample function.

Sub PreferenceCheck(var1 As String, var2 As String)
    If var1 = "No Preference" Or var2 = "No Preference" Then
        tempVar = true
    End If
    If Not tempVar Then
        If var1=var2 Then
            MsgBox ("Facilities "&var1&" and facilities "&var2&" cannot be the same. Please select another option for facilities preference, if you have none then select 'No preference'")
        End If
    End If
End Sub

Calling this function for 3 combinations

PreferenceCheck(cbo_fac1.Value, cbo_fac2.Value)
PreferenceCheck(cbo_fac2.Value, cbo_fac3.Value)
PreferenceCheck(cbo_fac1.Value, cbo_fac3.Value)

Upvotes: 0

Related Questions