And/Or operators in If-Then-ElseIf Statements Causing Mismatched Results

[nryan]

Hello,

It's been an arduous journey getting to where I am so far with my VBA code of cascading list boxes. I have two modules in my workbook: one for the cascading list boxes and one for outputting specific instructions based on what the user selects from the list boxes. The list boxes are working, but some of the outputs are mismatched. What I mean is the user is selecting one thing and the code is outputting another. This is most likely a problem with my If-Then-ElseIf statement in my second module. I'm using the Or operator and I'm probably not using it correctly. I'm trying to combine these:

    ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i40-SC" _
    Then
        [Option A]

    ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i50-SC" _
    Then
        [Option A]

     ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i67-SC" _
    Then
        [Option A]

Into this:

 ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i40-SC" Or Instructions.Range("ModelOutput").Value = "i50-SC" Or Instructions.Range("ModelOutput").Value = "i67-SC" _
    Then
        [Option A]

Notice how the only difference between the three are the "ModelOutput" Value. The goal is to save space, making my code a little cleaner. I don't have to combine them, but it would be nice because I have about 20 of these now and it would be nice to keep it at 20 rather than increase it to 60.

Thanks for your help.
-Nick

 

[JLGWhiz]

Hello,

It's been an arduous journey getting to where I am so far with my VBA code of cascading list boxes. I have two modules in my workbook: one for the cascading list boxes and one for outputting specific instructions based on what the user selects from the list boxes. The list boxes are working, but some of the outputs are mismatched. What I mean is the user is selecting one thing and the code is outputting another. This is most likely a problem with my If-Then-ElseIf statement in my second module. I'm using the Or operator and I'm probably not using it correctly. I'm trying to combine these:

    ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i40-SC" _
    Then
        [Option A]

    ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i50-SC" _
    Then
        [Option A]

     ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i67-SC" _
    Then
        [Option A]

Into this:

 ElseIf Instructions.Range("BodyOutput").Value = "Angle" _
        And Instructions.Range("CoaxConfigOutput").Value = "Single" _
        And Instructions.Range("GSSGOutput").Value = "" _
        And Instructions.Range("CoaxSizeOutput").Value = "0.023" _
        And Instructions.Range("ModelOutput").Value = "i40-SC" Or Instructions.Range("ModelOutput").Value = "i50-SC" Or Instructions.Range("ModelOutput").Value = "i67-SC" _
    Then
        [Option A]

Notice how the only difference between the three are the "ModelOutput" Value. The goal is to save space, making my code a little cleaner. I don't have to combine them, but it would be nice because I have about 20 of these now and it would be nice to keep it at 20 rather than increase it to 60.

Thanks for your help.
-Nick

You might get it to work like this:

And [COLOR="#B22222"]([/COLOR]Instructions.Range("ModelOutput").Value = "i40-SC" Or Instructions.Range("ModelOutput").Value = "i50-SC" Or Instructions.Range("ModelOutput").Value = "i67-SC"[COLOR="#B22222"])[/COLOR] _
    Then

But the ElseIf method is a lot easier to follow, logic wise.