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

You can use global variables such as Public checkbox1, checkbox2, checkbox3 as Long then you'll be able to sum values with valsum = Application.WorksheetFunction.Sum(checkbox1, checkbox2, checkbox3). — Vincent
– Vincent, Commented Jan 23, 2022 at 21:41
@Vincent should I call them in each checkbox or it should be added in the userform in this scenario. something like this? Private Sub UserForm_Click() Public checkbox1, checkbox2, checkbox3 as Long .... End Sub — Richard Fowler
– Richard Fowler, Commented Jan 23, 2022 at 21:49

Brian M Stafford · Accepted Answer · 2022-01-24 12:26:07Z

1

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

answered Jan 24, 2022 at 12:26

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2022-01-24 18:50:44Z

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...

Ambie · Accepted Answer · 2022-01-23 21:52:36Z

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

Close as there is no textbox at all, only checkbox and a label. gonna update an example

Collectives™ on Stack Overflow

Excel Userform sum Checkbox Values

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related