For repeated ComboBoxes Count Selected Items

Borhani · January 19th, 2008, 02:50 PM

:)

I have 40 ComboBoxes on a form... each ComboBox contains two items... for example item A and item B.... I have also added two textboxes on the form....I want one textbox to count total number of A items selected and the other textbox to count total number of B items selected....

Grateful if you could help and explain how to do above in Excel VBA

Thanks and Regards...A. Borhani

tstav · March 24th, 2008, 06:49 PM

Supposing your comboboxes are all named "ComboBox" plus the serial number 1, 2, 3, ...
You can loop through them and check the ListIndex property of each one. ListIndex=0 means first row selected, ListIndex=1 means second row selected.
There's a button in your Userform which when pressed counts the selected items and shows the result in two textboxes.

Code:

Private Sub btnCountComboSelections_Click()
Dim i As Integer, Count1 As Integer, Count2 As Integer
For i = 1 To 40 '<-- 40 ComboBoxes
   If Me.Controls("ComboBox" & i).ListIndex = 0 Then
      Count1 = Count1 + 1
   Else
      Count2 = Count2 + 1
   End If
Next
Me.TextBox1.Text = CStr(Count1)
Me.TextBox2.Text = CStr(Count2)
End Sub