Logical Error using Select Case Statement

O

oHoi99

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I am using Userform to let users select options to show Charts, but there seems to be a logical error. Is it correct for me to use the And function here to apply multiple conditions?
Could anyone help me to take a look at my code please?

Dim Selection
Dim a, b, c, d, e As Integer

a = Me.opt1.Value
b = Me.opt2.Value
c = Me.opt3.Value
d = Me.optBarChart.Value
e = Me.optPieChart.Value

Select Case Selection
Case a And d
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

End Select
End Sub

Why is my last case showing the Chart for the first case instead? I have tried using the IF ELSEIF statement, and I received the same result... :(
I wonder if I am allowed to use the AND function in Userform like this?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
You are using Option or CheckBox?

By default, you can choose only one of the options, not several, unlike CheckBox. Option button does not hold any value, but True or False only

If you want to use Select Case, you can do like this:

Select Case True

Case a=True and b=True
Call Data1_BarChart

Case a=True and e=True
Call Data1_PieChart

and so on.
 
Upvote 0
Because Select ... Case and If ... Then ... Else evaluate in order and stop evaluating as soon as one of the conditions is satisfied. The condition a And d is satisfied when a And b And d is satisfied. Because the other is first, it gets fired.

You can rewrite in a couple different ways. You can specify that b must be false in the first condition ...

VBA Code: 
Select Case Selection
Case a And d And Not b
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

End Select

... or reorder to put the most restrictive conditions first:

VBA Code: 
Select Case Selection
Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

Case a And d
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

End Select
 
Upvote 0
Solution
Zot said:
You are using Option or CheckBox?

By default, you can choose only one of the options, not several, unlike CheckBox. Option button does not hold any value, but True or False only

If you want to use Select Case, you can do like this:

Select Case True

Case a=True and b=True
Call Data1_BarChart

Case a=True and e=True
Call Data1_PieChart

and so on.
Click to expand...
Hi Zot, thanks for your reply! I am using CheckBox by the way. For checkbox, multiple selections can be done. I have tried using other selections, this is just an example of my codes.
 
Upvote 0
JonXL said:
Because Select ... Case and If ... Then ... Else evaluate in order and stop evaluating as soon as one of the conditions is satisfied. The condition a And d is satisfied when a And b And d is satisfied. Because the other is first, it gets fired.

You can rewrite in a couple different ways. You can specify that b must be false in the first condition ...

VBA Code: 
Select Case Selection
Case a And d And Not b
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

End Select

... or reorder to put the most restrictive conditions first:

VBA Code: 
Select Case Selection
Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

Case a And d
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

End Select
Click to expand...
Hi Jon, thanks for your reply!
I see, I am very new to VBA, and did not know I can use a Not function too. I will try it out. Thank you very much for the advice :D
 
Upvote 0
JonXL said:
Because Select ... Case and If ... Then ... Else evaluate in order and stop evaluating as soon as one of the conditions is satisfied. The condition a And d is satisfied when a And b And d is satisfied. Because the other is first, it gets fired.

You can rewrite in a couple different ways. You can specify that b must be false in the first condition ...

VBA Code: 
Select Case Selection
Case a And d And Not b
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

End Select

... or reorder to put the most restrictive conditions first:

VBA Code: 
Select Case Selection
Case a And b And d
Call Data1_and_Data2_BarChart
'Shows Bar Chat for Data 1

Case a And d
Call Data1_BarChart
'Shows Bar Chat for Data 1

Case a And e
Call Data1_PieChart
'Shows Pie Chat for Data 1

Case b And d
Call Data2_BarChart
'Shows Bar Chat for Data 2

Case b And e
Call Data2_PieChart
'Shows Pie Chat for Data 2

End Select
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
How to make a Case Statement Loop through Column
Replies
3
Views
545
Tash_081
T
R
  • Question
I need to return a lookup on a table once a slicer has been selected.
Replies
3
Views
386
JamesCanale
JamesCanale
K
  • Question
Case sensitive distinct count based on multiple criteria
Replies
2
Views
247
shaowu459
shaowu459
K
  • Locked
  • Question
Vba code for User history log which is giving me a run-time error "13" mismatch
Replies
2
Views
101
Fluff
Fluff
M
  • Question
What am I doing Wrong?
Replies
6
Views
212
MBarrett
M

Forum statistics

Threads
1,221,819
Messages
6,162,155
Members
451,749
Latest member
zack_ken

Share this page

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