Count The Number of UserForm ComboBoxes Which Contain Specific Text

[Nadine67]

Hello and thank you for any attention my post may receive.

So I have an excel userform (UserForm1) with various controls and I would like to count the number of comboboxes (cbx) which contain the word 'Yes' and report the count in Label82 on the form. There are 24 cbx on UserForm1 and are numbered 1 - 24.

To make this even more tricky I would like the CountIf to execute once TextBox57 is no longer empty.

Is this possible?

Thank you and have a great day!

 

[Rick Rothstein]

@Nadine67,

Make sure you read Messages #9 and #10


@GTO,

My approach to the Class solution is a bit different from yours (a touch more compact as I am wont to favor)...

Place in a Class Module (named Class1)

Public WithEvents ComboBoxGroup As ComboBox

Private Sub ComboBoxGroup_Change()
  UserForm1.ShowYesCount
End Sub

Place in a UserForm

Dim objComboBoxes() As New Class1

Private Sub TextBox57_Change()
  ShowYesCount
End Sub

Private Sub UserForm_Initialize()
  Dim cbCount As Long, Ctrl As Control
  For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "ComboBox" Then
      Ctrl.List = [{"Yes","No","Maybe"}]
      If Mid(Ctrl.Name, 9) <= 24 Then
        cbCount = cbCount + 1
        ReDim Preserve objComboBoxes(1 To cbCount)
        Set objComboBoxes(cbCount).ComboBoxGroup = Ctrl
      End If
    End If
  Next
  Set Ctrl = Nothing
End Sub

Sub ShowYesCount()
  Dim X As Long, Count As Long
  If Len(UserForm1.TextBox57.Text) Then
    For X = LBound(objComboBoxes) To UBound(objComboBoxes)
      If LCase(objComboBoxes(X).ComboBoxGroup.Value) = "yes" Then
        Count = Count + 1
      End If
    Next
    UserForm1.Label82.Caption = Count
  Else
    UserForm1.Label82.Caption = ""
  End If
End Sub

 

[Nadine67]

Then added the 'ShowYesCount' to the two controls Change event.

Sorry for the confusion on my part. By this I meant the Txtbox and Label. I did add the code to all 24 cbx. The reason why your code didn't do as it should was because I didn't do as you said. I apologise for that. I actually added the TextBox57 code to the incorrect txtbox.

I picked it up when I came back in to work this morning and corrected my error. And presto.....the count is working as it should.

Thank you so much for your patience and help Rick. Have a great day!

 

[Nadine67]

**** solved ****

 

[Nadine67]

Thank you very much for this GTO. Much appreciated!