Excel Userform sum Checkbox Values

Question

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

Answer 1

Something like below, I do not know how you fill them, but basically this should work. Combobox1_change etc. starts event when combobox value is changed, then each even checks if value is numeric and calls a method that sums up the values and assignes it to label

Private Sub UserForm_Initialize()
For i = 1 To 10
    ComboBox1.AddItem (i)
    ComboBox2.AddItem (i)
    ComboBox3.AddItem (i)
Next i
End Sub
Private Sub ComboBox1_Change()
'call sum and display method
Call Calc
End Sub
Private Sub ComboBox2_Change()
'call sum and display method
Call Calc
End Sub
Private Sub ComboBox3_Change()
'call sum and display method
Call Calc
End Sub
Private Sub Calc()
'method to update
Dim total As Double
'check if value of combobox is numeric
If IsNumeric(ComboBox1.value) Then
  total = total + ComboBox1.value
End If
If IsNumeric(ComboBox2.value) Then
    total = total + ComboBox2.value
End If
If IsNumeric(ComboBox3.value) Then
    total = total + ComboBox3.value
End If
Label1 = total
End Sub

EDIT Ok, I just noticed I was tired and I misunderstood the question completely, sorry :( Now, I cannot remember where I got Combobox values from...

Answer 2

It looks as if you're trying to assign a numerical value to your CheckBoxes. Am I seeing that correctly? You can pretty much only assign a True or False value to a CheckBox (you can have tri-state ones which take a Null value too). It's true that booleans are 1 or 0, but you can't assign +1, 0 or -1 to them.

I think you need to have 3 Checkboxes and then an associated control for each checkbox which contains the numerical value (eg a TextBox or ComboBox). You'd probably then enable or disable those associated controls depending on whether the CheckBox was ticked.

Assuming a UserForm that looked like below:

Your skeleton code could be something like this:

Option Explicit

Private Sub CheckBox1_Change()
    EnableTextBox TextBox1, CheckBox1
End Sub

Private Sub CheckBox2_Change()
    EnableTextBox TextBox2, CheckBox2
End Sub

Private Sub CheckBox3_Change()
    EnableTextBox TextBox3, CheckBox3
End Sub

Private Sub EnableTextBox(tbox As MSForms.TextBox, chbox As MSForms.CheckBox)
    If chbox.Value Then
        With tbox
            .Enabled = True
            .Text = ""
        End With
    Else
        With tbox
            .Enabled = False
            .Text = "n/a"
        End With
    End If
End Sub

Private Sub CommandButton1_Click()
    Dim tot As Double
    
    tot = TextToDouble(TextBox1.Text) + TextToDouble(TextBox2.Text) + TextToDouble(TextBox3.Text)
    Label1.Caption = tot
End Sub

Private Function TextToDouble(txt As String) As Double
    On Error Resume Next
    TextToDouble = CDbl(txt)
End Function