Multiple Conditions in an IF statement

[ClarissaG]

Hi All

This is a simple query I'm sure, but I'm new to VBA and can't get the syntax right.

I'm trying to construct an IF statement which involves multiple OR conditions along the lines of:

If CheckBox1 = true
OR
CheckBox2 = true
OR
CheckBox3 = true Then
var_name = 1
Else
var_name = 2

Could someone put me out of my misery please?

Thank You

ClarissaG

[rvExcelNewTip]

=if(checkbox1+checkbox2+chechbox3,var_name=1,var_name=2)

[ClarissaG]

That looks like a worksheet formula.

I'm looking for some VBA behind a form.

Sorry if I didn't make it clear

Thanks

ClarissaG

[rvExcelNewTip]

Select Case CheckBox1+CheckBox2+CheckBox3

Case True
var_name=1

Case False
car_name=2

End Select

But I'm not sure what you want to accomplish.
Also, you may have to qualify the Checkbox name with the WorksheetName e.g.
WorkSheets("Sheet1").Checkbox1 or ActiveSheet.Checkbox1

[ClarissaG]

I appreciate your help, but I haven't mentioned worksheets. This is a vba userform problem.

This is what I'm trying to achieve. The logic is pretty simple, but I'm having trouble translating this logic into code.

I have 5 checkboxes on a form. Let's call them CB1, CB2, CB3, CB4, CB5. These checkboxes turn on and off tabpages. Let's call them TP1, TP2, TP3, TP4, TP5. When CB1 is checked, TP1 appears.When it's unchecked, TP1 disappears. This is no problem.

The complexity is that when any one of the CBs is checked, in addition to their TP appearing, another tab page has to appear. Let's call it TP_Any.

So if any CBs are checked, then their corresponding TPs are visible plus tabpage TP_Any. There is only one instance of TP_Any. It will appear if any one or more CB is checked, and will disappear only if all CBs are unchecked.

I can get it to work so that checking one CB makes its TP and TP_Any appear. And if that CB is uchecked, both tabpages disappear. But if two CBs are checked, and one is then unchecked, I need the TP_Any to remain as there is still a CB checked.

So the question is: what is the simplest way of creating this code without duplicating multiple conditions for each CB_change event?

I've tried the Case statement, but it doesn't work. Why are there + signs? I thought these denoted only addition or text concatenation in variants. That threw me.

Any help would be greatly appreciated.

Clarissa.

[rvExcelNewTip]

If I understand correctly, any combination of the 5 switches is possible, including multiple checkboxes on similtaneously. If that's the case then I'm afraid the brute force attack, considering all possible combinations is probably the "cleanest" approach to the problem.

I suggest you to attach a binary value (1,2,4,8 and 16) to each of the checkboxes.
Then you define a Status Variable, within the scope of the Userform that contains the checkboxes.
Within the Checkbox_change event, increase that variable with the corresponding amount when the box turns on, decrease it when it turns off. Then Call an internal procedure that does the hiding/unhiding of the worksheets.

One snag: You cannot hide ALL worksheets within a workbook. So you will have to provide a "guard" worksheet or you'll have to make un exception to your rules.

Sample code for three checkboxes attached: Boxes are called c1,c2 and c3; worksheets are called s1,s2 and s3 (and of course: any)

Private intStatus As Integer

Private Sub C_Action(intStatus As Integer)

'If any box is on, then show "Any"-Sheet
Select Case c1 + c2 + c3
Case True
Worksheets("any").Visible = True
Case False
Worksheets("any").Visible = False
End Select

Select Case intStatus
Case 0 ' all off, see above
'*** At least one worksheet in the workbook
'*** must remain visible
Case 1 ' c1 on, other off
Worksheets("s1").Visible = True
Worksheets("s2").Visible = False
Worksheets("s3").Visible = False
Case 2 ' c2 on, others off
Worksheets("s1").Visible = False
Worksheets("s2").Visible = True
Worksheets("s3").Visible = False
Case 3 ' both c1 & c2 on, other off
Worksheets("s1").Visible = True
Worksheets("s2").Visible = True
Worksheets("s3").Visible = False
Case 4 ' c3 on, others off
Worksheets("s1").Visible = False
Worksheets("s2").Visible = False
Worksheets("s3").Visible = True
Case 5 ' c3 & c1 on , c2 off
Worksheets("s1").Visible = True
Worksheets("s2").Visible = False
Worksheets("s3").Visible = True
Case 6 ' c3 & c2 on, c1 off
Worksheets("s1").Visible = False
Worksheets("s2").Visible = True
Worksheets("s3").Visible = True
Case 7 ' All on
Worksheets("s1").Visible = True
Worksheets("s2").Visible = True
Worksheets("s3").Visible = True
End Select

End Sub

Private Sub c1_Change()
Select Case c1
Case True
intStatus = intStatus + 1
Case False
intStatus = intStatus - 1
End Select

Call C_Action(intStatus)
End Sub

Private Sub c2_Change()
Select Case c2
Case True
intStatus = intStatus + 2
Case False
intStatus = intStatus - 2
End Select
Call C_Action(intStatus)
End Sub

Private Sub c3_Change()
Select Case c3
Case True
intStatus = intStatus + 4
Case False
intStatus = intStatus - 4
End Select
Call C_Action(intStatus)
End Sub

In your case you have 32 possibilities, but the code is essentially the same, albeit somewhat longer.

PS: The + sign acts as on OR operator on Boolean values. c1+c2+c3 means if any of the boxes is On

[Norie]

Clarissa

Is it not just something like this.

Please Login or Register  to view this content.

[rvExcelNewTip]

5 checkboxes and 32 combinations is probably a maximum any programmer can support with cut and paste techniques. If you were to have 10 checkboxes, then the number of combinations would grow to 1024 and the code I posted earlier, through extendable in principle, would become very cumbersome.

With some conventions, to be agreed upon, for the worksheetnames we can reduce the code to a fixed amount, independent of the number of checkboxes.

Here it goes: Worksheetnames consist of a root part concatenated with a number that corresponds to its checkboxnumber. e.g. S1 is a worksheetname with a root "S" corresponding to checkbox number 1.

A status variable is used in the same way as described in the earlier code. This program replaces the earlier one (for 3 checkboxes).

Private Sub C_Action(intStatus As Integer)

Const c_intNCb As Integer = 3 'Number of check boxes
Const c_strWsRoot As String = "S" 'Worksheet Name Root

'If any box is on, then show "Any"-Sheet
Select Case c1 + c2 + c3
Case True
Worksheets("any").Visible = True
Case False
Worksheets("any").Visible = False
' *** Hide all but one Worksheet - exception code to
' be inserted here *********************************
End Select

Dim intMask As Integer
intMask = 1

Dim intL As Integer
For intL = 1 To c_intNCb

Select Case (intStatus And intMask) = intMask
Case True 'show worksheet
Worksheets(c_strWsRoot & CStr(intL)).Visible = True
Case False 'hide worksheet
Worksheets(c_strWsRoot & CStr(intL)).Visible = False
End Select

intMask = intMask * 2

Next

End Sub

If more boxes are used, change the constant c_intNCb and the test C1+C2+C3+ ... to whatever number of boxes you have. (If you have more than 15 checkboxes use Long Variables!)