Count The Number of UserForm ComboBoxes Which Contain Specific Text

N

Nadine67

Board Regular
Joined
May 27, 2015
Messages
225
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Presuming that the combo boxes's .List is populated, let us say with 'Yes', 'No' and 'Maybe', should we assumed that you want a count of how many combo boxes currently have the value 'Yes' selected?
 
Upvote 0
GTO said:
Presuming that the combo boxes's .List is populated, let us say with 'Yes', 'No' and 'Maybe', should we assumed that you want a count of how many combo boxes currently have the value 'Yes' selected?
Click to expand...
In addition to GTO's question...

1) What are the names of your comboboxes... ComboBox1, ComboBox2, etc. or cbx1, cbx2, etc.?

2) What should happen if the text in TextBox57 is deleted... should Label82 become blank?

3) I presume if TextBox57 has text in it, and the count is displayed in Label82, that the count should be updated whenever a combobox value changes to or from a "yes" value, correct?
 
Upvote 0
Hi Rick

1) the names are the generic ComboBox1,'ComboBox2, ComboBox3, .........., ComboBox24.
2) Hmmm I didn't think of this.
3) That would be ideal, but would there be lag in between combobox changes? Let's go with this if there is no lag.
 
Upvote 0
Nadine67 said:
Hi Rick

1) the names are the generic ComboBox1,'ComboBox2, ComboBox3, .........., ComboBox24.
2) Hmmm I didn't think of this.
3) That would be ideal, but would there be lag in between combobox changes? Let's go with this if there is no lag.
Click to expand...
Put this subroutine in the UserForm's code window...
Code: 
[table="width: 500"]
[tr]
	[td]Sub ShowYesCount()
  Dim X As Long, Count As Long
  If Len(TextBox57.Text) Then
    For X = 1 To 7
      If LCase(Controls("ComboBox" & X).Value) = "yes" Then Count = Count + 1
    Next
    Label82.Caption = Count
  Else
    Label82.Caption = ""
  End If
End Sub[/td]
[/tr]
[/table]
and then put this...

ShowYesCount

in the Change event procedure for TextBox57 and for the Change event procedures for each one of your 24 ComboBoxes. That's it... the Label should now display what you want when the UserForm is showing.
 
Upvote 0
Similar to Rick's, but I was thinking a class for the appropriate combo boxes and using the .Tag property to ID them.

Something like...

In the Userform's module:

Rich (BB code): 
Option Explicit
  
Dim ControlArray() As Class1
  
Private Sub TextBox57_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim CTL As MSForms.Control
Dim lCount As Long
  
  If Len(Me.TextBox57.Value) Then
    For Each CTL In Me.Controls
      If CTL.Tag = "MyGroup" Then
        If CTL.Value = "YES" Then
          lCount = lCount + 1
        End If
      End If
    Next
  End If
  
  Me.Label82.Caption = IIf(CBool(lCount), lCount, vbNullString)
  
End Sub
  
Private Sub UserForm_Initialize()
Dim CTL As MSForms.Control
Dim bolInitiated As Boolean
  
  For Each CTL In Me.Controls
    If CTL.Tag = "MyGroup" Then
      CTL.List = Array("YES", "NO", "MAYBE")
      CTL.Style = fmStyleDropDownList
      
      If Not bolInitiated Then
        bolInitiated = True
        ReDim ControlArray(0 To 0)
        Set ControlArray(0) = New Class1
        Set ControlArray(0).ParentForm = Me
        Set ControlArray(0).ThisComboBox = CTL
      Else
        ReDim Preserve ControlArray(0 To (UBound(ControlArray) + 1))
        Set ControlArray(UBound(ControlArray)) = New Class1
        Set ControlArray(UBound(ControlArray)).ParentForm = Me
        Set ControlArray(UBound(ControlArray)).ThisComboBox = CTL
      End If
    End If
  Next
      
End Sub

In a Class Module (named: 'Class1') :

Rich (BB code): 
Option Explicit
  
Private WithEvents CBO As MSForms.ComboBox
Private Form As Object
  
Public Property Set ParentForm(pf As Object)
  Set Form = pf
End Property
  Public Property Get ParentForm() As Object
    Set ParentForm = Form
  End Property
  
Public Property Set ThisComboBox(cb As MSForms.ComboBox)
  Set CBO = cb
End Property
  Public Property Get ThisComboBox() As MSForms.ComboBox
    Set ThisComboBox = CBO
  End Property
  
Private Sub CBO_Change()
Dim lCount As Long
  
  If Len(ParentForm.TextBox57.Value) Then
    lCount = GetCount
    ParentForm.Label82.Caption = IIf(CBool(lCount), lCount, vbNullString)
  End If
  
End Sub
  
Private Function GetCount() As Long
Dim CTL     As MSForms.Control
Dim lCount  As Long
  
  For Each CTL In ParentForm.Controls
    If CTL.Tag = "MyGroup" Then
      If CTL.Value = "YES" Then
        lCount = lCount + 1
      End If
    End If
  Next
  
  GetCount = lCount


End Function


To change the .Tag property, in design time, you would select all the appropriate combo boxes (CTL + Click on each) and enter the Tag value once.

Mark
 
Upvote 0
Hmmmm, Rick I don't get any count happening in the TextBox57 nor the Label. Nothing appears in either controls. Rick excuse of my ignorance, but what does 'For X = 1 To 7' in your code mean? Why 1 to 7?

I added the subroutine to the Userform by copying it directly from above and doubleclicking on the form and paste. Then added the 'ShowYesCount' to the two controls Change event.
 
Upvote 0
Nadine67 said:
Hmmmm, Rick I don't get any count happening in the TextBox57 nor the Label. Nothing appears in either controls. Rick excuse of my ignorance, but what does 'For X = 1 To 7' in your code mean? Why 1 to 7?
Click to expand...
Sorry, I meant to change the 7 to 24 (I only used 7 ComboBoxes on my UserForm while developing the code). Try making that change and see if the code works for you then. By the way, I assumed the count would be in the Label only... I further assumed the text in the TextBox would be manually entered... is that not the case?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Excel VBA- Viewing and Updating existing worksheet data with Excel UserForm with Dependent Comboboxes
Replies
2
Views
841
charliechaz
C
I
  • Solved
Userform to show only specific data in combobox dependant on other combobox selection
2 3 4
Replies
35
Views
1K
dmt32
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A
S
  • Solved
Vba code help, userform combobox input selection to specific cell.
Replies
9
Views
745
dmt32
D
R
  • Question
Define a cell containing specific text as range
Replies
2
Views
355
Rainmanne
R

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top