IF THEN ELSE Statement in Userform

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Hi, I have created a Userform where I let the users select options to show charts based on their selection.
But I am facing some problem with the IF statement in VBA. I wonder if I used the IF statement correctly? Because it doesn't seem to be working as I expect.
I thought only if the conditions are true, then it will execute the code after its Then? And if it does not satisfy the condition, it will move on to the next ElseIf, and so on. Please refer to the codes below to see if the way of using IF statements is correct or not. The code is running smoothly without showing any error, but showing undesired output.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub OptionSelection

Dim k As String

With Sheet1

If Me.optCELL1.Value And Me.optOverallType.Value And Me.optInvOut.Value Then '(After selecting CELL1, OverallType and InvOut, Excel correctly shows the chart for this selection)
Call Cell1_Overall_InvOut

ElseIf Me.optCELL2.Value And Me.optOverallType.Value And Me.optInvOut.Value Then '(After selecting CELL2, OverallType and InvOut, Excel correctly shows the chart for this selection)
Call Cell2_Overall_InvOut

ElseIf Me.optCELL1.Value And Me.optCELL2.Value And Me.optOverallType.Value And Me.optInvOut.Value Then
Call Cell1AndCell2_InvOut
'(By right, after selecting CELL1, CELL2, OverallType and InvOut, Excel will show the chart for both CELL1 and CELL2, but it shows the chart for CELL 1 only, without CELL2 (which is the same as the first IF statement. I have checked the Cell1AndCell2_InvOut sub and run my code there, it does show the chart for both CELL1 and CELL2, which means there is no problem with the VBA codes there, but there is a logic error occurring in my IF statement)
Else
k = "Insufficient Selections Made"
MsgBox k

End If
End With
End Sub
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

There is no problem when there are no repeating conditions. But when it is like this, the 2nd ELSEIF condition is exactly the same as the 1st IF condition,
with only 1 more extra condition. But then it only reads Cell1, Overall and InvOut conditions, and ignores Cell2 condition, and eventually only shows the results
for the first statement. Does anyone have any idea on what and where is the logic error here?
Hope to receive help as soon as possible.

Best Regards.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
Looks like you to need to rearrange your IFs ...

At the moment, if your third test is TRUE (i.e. Me.optCELL1.Value And Me.optCELL2.Value And Me.optOverallType.Value And Me.optInvOut.Value) then the first test must also be TRUE (i.e. Me.optCELL1.Value And Me.optOverallType.Value And Me.optInvOut.Value)

Hence your code will call Cell1_Overall_InvOut, and not get to the ElseIf's.
 
Solution

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
woks like you to need to rearrange your IFs ...

At the moment, if your third test is TRUE (i.e. Me.optCELL1.Value And Me.optCELL2.Value And Me.optOverallType.Value And Me.optInvOut.Value) then the first test must also be TRUE (i.e. Me.optCELL1.Value And Me.optOverallType.Value And Me.optInvOut.Value)

Hence your code will call Cell1_Overall_InvOut, and not get to the ElseIf's.
Hi Stephen. I see. Thanks for pointing out the mistake! Really appreciate it. I will try it out :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

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
Top