How can I sum up several check boxes conditioned by a combo box?

[balan_radu2003]

Hello,

So I have a Form and in there I have several check boxes. Some of them
have the same value, like 1 $. I want when I choose more check boxes
he multiplies the quantity of check boxes with 1 $.

Example:

Check Box 1 is checked, Check Box 4 is checked, Check Box 7 is also
checked. The Check Boxes in between are not checked. All this Check
Boxes (from 1 to 7) have the value of 1 $. So because I choose 1, 4
and 7 I will have the Sum of 3 $. If I choose also Check Box 5 for
example the sum should change into 4 $ and so on. But these Check
Boxes are conditioned by a Size Combo Box with the values: "little",
"middle" and "big"... so if I choose "middle" in the Combo box the
value of the Check Boxes which is the same for all check boxes becomes
now 2 $. So if I have chosen "middle" in the Combo Box and the Check
Box 1 is checked, Check Box 4 is checked and Check Box 7 is also
checked then he has to add 2$ + 2$ + 2$. Same story for "big" but the
Check boxes have now the value of 3$.... (the 1$ example was for the
"little" value of the combo box)

I already thought of a code like this, but which is to long if I would
make it for all Check Boxes and all Combo Box values, and I want a
shorter version if it is possible:

total = 0
If (CheckBox1 = True) Then
If (cboCourse = "Big") Then
total = total + 3
End If
End If
If (CheckBox2 = True) Then
If (cboCourse = "Big") Then
total = total + 3
End If
End If
If (CheckBox3 = True) Then
If (cboCourse = "Big") Then
total = total + 3
End If
End If
If (CheckBox4 = True) Then
If (cboCourse = "Big") Then
total = total + 3
End If
End If
.....
If (CheckBox1 = True) Then
If (cboCourse = "middle") Then
total = total + 2
End If
End If
If (CheckBox2 = True) Then
If (cboCourse = "middle") Then
total = total + 2
End If
End If
.....
If (CheckBox1 = True) Then
If (cboCourse = "little") Then
total = total + 1
End If
End If
If (CheckBox2 = True) Then
If (cboCourse = "little") Then
total = total + 1
End If
End If
.....
ActiveCell.Offset(0, 9).Value = total


Please use also the codes: "total = 0" and "ActiveCell.Offset(0,
9).Value = total"


Thank you very much!

Radu

 

[balan_radu2003]

any ideas?

 

[Dave Peterson]

I'm not sure I understand, but maybe...

Can't you just total up the number of checkboxes that are checked.

The multiply that number by 1, 2 or 3--based on the value of the single
combobox.

Option Explicit
Private Sub CommandButton1_Click()

Dim mySum As Long
Dim myTotal As Long
Dim myMultiplier As Long

mySum = 0
'in VBA true = -1, so we subtract!
mySum = mySum - CBool(Me.CheckBox1.Value = True)
mySum = mySum - CBool(Me.CheckBox2.Value = True)
mySum = mySum - CBool(Me.CheckBox3.Value = True)

Select Case LCase(Me.ComboBox1.Value)
Case Is = LCase("small"): myMultiplier = 1
Case Is = LCase("middle"): myMultiplier = 2
Case Is = LCase("big"): myMultiplier = 3
Case Else
myMultiplier = 0 '???
End Select

myTotal = mySum * myMultiplier

MsgBox myTotal

End Sub

 

[balan_radu2003]

Thank you very much! It was exactly what I needed!

 

[balan_radu2003]

Thank you very much! It was exactly what I needed!