Search code examples
excelvbauserform

Excel Userform sum Checkbox Values

Currently I am working in a "Score Card" in a userform where the user will have A list of options and a check box next to each other. If the checkbox is "False" the value will be 0 or if the checkbox is "True" the value could go from -3 to 3.

I tried to use something like this:

Private Sub checkbox1_Click()
If checkbox1 = True Then
checkbox1 = 1
Else
checkbox1 = 0 
End Sub
'**********************************
Private Sub checkbox2_Click()
If checkbox2 = True Then
checkbox2 = -1
Else
checkbox2 = 0 
End Sub
'**********************************
Private Sub checkbox3_Click()
If checkbox3 = True Then
checkbox3 = 1
Else
checkbox3 = 0 
End Sub

While the user select the checkbox, it will sum all of the values and provide a final result in a autogenerated label within the userform

Private Sub UserForm_Click()
valsum = Sum checkbox1, checkbox2, checkbox3 ' sums all the checkboxes
if valsum > 0 then
label1.caption = "Good to Go"
elseif valsum = 0 then
label1.caption = "Caution"
else 'if < 0
label1.caption = "You are Bad"
end Sub

I will like to avoid having data in worksheets and just have everything run "back end" EDIT: This is how it should look. The value should be predefied for example if checkbox1 is true, then cb1= 3, else cb1=0. If checkbox2 is true then cb2=1.....and then sum those values together

This is how it should look

Solution

  • Something like the following appears to be what you need:

    Option Explicit

Private valsum As Integer

Private Sub checkbox1_Click()
   SumAllValues
End Sub

Private Sub checkbox2_Click()
   SumAllValues
End Sub

Private Sub checkbox3_Click()
   SumAllValues
End Sub

Private Sub checkbox4_Click()
   SumAllValues
End Sub

Private Sub SumAllValues()
   valsum = 0
   valsum = valsum + IIf(CheckBox1.Value = True, 3, 0)
   valsum = valsum + IIf(CheckBox2.Value = True, 1, 0)
   valsum = valsum + IIf(CheckBox3.Value = True, -2, 0)
   valsum = valsum + IIf(CheckBox4.Value = True, 2, 0)
   
   If valsum > 0 Then
      Label1.Caption = "Good to Go"
   ElseIf valsum = 0 Then
      Label1.Caption = "Caution"
   Else 'if < 0
      Label1.Caption = "You are Bad"
   End If
End Sub