I have an Excel Userform which has 7 comboboxes with the options N/A, Yes, No and Partially. I would like to create a total score based on the responses in each of the comboboxes in a label on the form that would calculate as the boxes are updated or on a button click(really doesn't matter). Yes = 1 Partially = 0.5 To get the score I also need a count of the yes and Partially results as the score will be the sum of Yes and Partially/7.
I can't seem to get a label to update with any basic results however.
I have tried looking for code that's already out there but everything I find is referencing the actual excel worksheet/cells and not the immediate results in the userform or they are adding actual numbers and not converting text results to a number to be calculated.
This nested if statement is just my most recent attempt at trying to get even one of the comboboxes to populate into a label for me with no luck.
Private Sub Score_Change()
If CBCoverage.Value = "Yes" Then LBLCYes = 1
Else: LBLCYes = 0
If CBCoverage.Value = "Partially" Then LBLCPartially = 0.5
Else: LBLCPartiall = 0
End If
End If
End If
End Sub
I don't expect you to write the whole thing for me but if you could just help me see what I am doing wrong here that would be amazing! My comboboxs are named: CBCoverage CBInvestigation CBFinancials CBAutoPD CBEvaluation CBDocumentation CBCommunication
I attached this code to a button the form. It loops through each combobox on the form so if there are some you want to exclude the loop would need amending.
Private Sub CommandButton1_Click()
Dim c As Control, nYes As Long, nPartial As Long
For Each c In Me.Controls
If TypeName(c) = "ComboBox" Then
If c.Value = "Yes" Then nYes = nYes + 1
If c.Value = "Partially" Then nPartial = nPartial + 1
End If
Next c
MsgBox "There were " & nYes & " Yes, and " & nPartial & " Partial answers."
End Sub
Based on nYes and nPartial you could calculate an overall weighted score, e.g.
nYes + nPartial * 0.5