combinations of checkboxes and executing commands

[dreamz]

let's say i have 4 checkboxes, a, b, c, and d. i want to do some
calculation (e.g. the average) based on what is selected.

in other words, if a and b are chosen, it will be the average of a and
b. if a, b, and c are chosen, then it will be the average of a, b, and
c.

now, of course, if i hard code this as a bunch of if-then statements,
it will be cumbersome, as i'll have to code every single possible
combination (a; b; c; d; a and b; a and c; etc.).

is there a more efficient way to do this?

thanks.

 

[dreamz]

nevermind, i have a question.

how do i make it so that the formula is pasted into a cell rather than
a value?

 

[Guest]

if chkA.value then
wcount=wcount+1
dValue = dValue + A
endif
if chkB.value then
wcount=wcount+1
dValue = dValue + B
endif
if chkC.value then
wcount=wcount+1
dValue = dValue + C
endif

and so on.
then average is dvalue/wcount.
Hope this helps.

Alok

 

[Guest]

Hi,
The way to do it is
Worksheets("abc").Cells(1,2).FormulaR1C1="=Sum(myrange)"

By the way when you are asking a new question it is advisable to start a new
thread. Otherwise people may not pay attention to your new query.

Alok

 

[dreamz]

thanks, alok. i wrote something like this. to be clear, i declare
tricor and oos as ranges.


Code
-------------------
If radOverall Then

If chTricor Then
Set Tricor = Range("RankTricorAll")
TricorCount = 1
Else
Set Tricor = shRankings.Range("B3")
TricorCount = 0
End If

If chOOS Then
Set OOS = Range("RankOOSAll")
OOSCount = 1
Else
Set OOS = shRankings.Range("B3")
OOSCount = 0
End If

shRankings.Range("AverageAll").Formula = Tricor + OOS

End I
-------------------


and that didn't work. it said type mismatch error. what did i do wrong
(don't worry about the count stuff for now. i didn't include it in th
formula because i'm still testing it).


and it's not an entirely new question since it's just a problem
encountered when trying to implement the solution to this one. i don'
like creating multiple threads, as it seems like spam