CheckBox Conundrum

[Guest]

Hi Every1,

I have a problem that I am unable to think of a solution - I have a workbook
that has an analysis tab on the analysis tab I have three checkboxes which I
am wnating to use to chnage the value of cells, however I want to be able to
use a combination of these check boxes - I know how to set the value of a
cell and I know I can use "IF, then, else" etc in conjunction with 1 check
box but I am stuck with multiples i.e.

Scenario

Checkbox1 value = True
Checkbox2 vlue = False
Checkbox3 value = False

Cell c1 = 100

Else

Checkbox1 value = True
Checkbox2 vlue =True
Checkbox3 value = False

Cell c1 = 200

Else

Checkbox1 value = True
Checkbox2 vlue = False
Checkbox3 value = True

Cell c1 = 300


Checkbox1 value = True
Checkbox2 vlue = True
Checkbox3 value = True

Msgbox "Error"

I know this is a simple example but I think it explains what I am trying to
acheive, doesn't it?

Any pointers would be greatly appreciated.

Tia

 

[Guest]

You can use a little bit of "boolean math" to do this: to illustrate, let's
link checkbox1 to cell A1, checkbox2 to A2, checkbox3 to A3. Then in cell B1
I can put this formula (or you could calculate this in VBA):
= (A1*1)+(A2*2)+(A3*4)
I am treating the checkbox results as if they are binary digits. There are
8 possible combinations and the result is a number from 0-7 (you can check
this out by looking at how B1 responds as you change the checkboxes):
None checked: B1=0
checkbox 1 only: B1=1
checkbox 2 only: B1=2
1 & 2 checked: B1=3
3 only: B1=4
1 & 3: B1=5
2 & 3: B1=6
1, 2 & 3: B1=7
You can then use a CHOOSE function (either worksheet function or VBA) or a
Select Case in VBA to make a decision based on these results; for the example
you listed:
Dim CBoxes as Byte
CBoxes = Checkbox1.Value + Checkbox2.Value * 2 + Checkbox3.Value * 4
Select Case CBoxes
Case 1
Range("C1") = 100
Case 3
Range("C1") = 200
Case 2
Range("C1") = 300
Case 7
Msgbox "Error"
....

 

[Guest]

Wow, thanks "K" I'll give it a try

You can use a little bit of "boolean math" to do this: to illustrate, let's
link checkbox1 to cell A1, checkbox2 to A2, checkbox3 to A3. Then in cell B1
I can put this formula (or you could calculate this in VBA):
= (A1*1)+(A2*2)+(A3*4)
I am treating the checkbox results as if they are binary digits. There are
8 possible combinations and the result is a number from 0-7 (you can check
this out by looking at how B1 responds as you change the checkboxes):
None checked: B1=0
checkbox 1 only: B1=1
checkbox 2 only: B1=2
1 & 2 checked: B1=3
3 only: B1=4
1 & 3: B1=5
2 & 3: B1=6
1, 2 & 3: B1=7
You can then use a CHOOSE function (either worksheet function or VBA) or a
Select Case in VBA to make a decision based on these results; for the example
you listed:
Dim CBoxes as Byte
CBoxes = Checkbox1.Value + Checkbox2.Value * 2 + Checkbox3.Value * 4
Select Case CBoxes
Case 1
Range("C1") = 100
Case 3
Range("C1") = 200
Case 2
Range("C1") = 300
Case 7
Msgbox "Error"
...