CheckBox Conundrum

[Jonathan]

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

[K Dales]

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"
....

--
- K Dales


"Jonathan" wrote:

> 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

[Jonathan]

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

"K Dales" wrote:

> 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"
> ...
>
> --
> - K Dales
>
>
> "Jonathan" wrote:
>
> > 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