Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Conundrum
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Conundrum
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CheckBox Conundrum
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conundrum | Excel Discussion (Misc queries) | |||
VBA conundrum | New Users to Excel | |||
Calculation Conundrum | Excel Discussion (Misc queries) | |||
ISERROR Conundrum | Excel Worksheet Functions | |||
ISERROR Conundrum | Excel Worksheet Functions |