How to count an item with a specific string in an Excel VBA listbox?

Question

I have here a simple form that displays items from a sheet through a ListBox1 during Initialize.

This is this the image of raw data:

This is the text data:

Name        Letter
James       A
Mary        A
Robert      B
Patricia    C
John        C
Jennifer    C
Michael     D
Jennifer    E
David       E

This is my whole simple code. What I would like to achieve is to count the instance/occurrence of the letters shown in ListBox1. I know that we can use the count function for ranges from a sheet. But I would like to explore if we can count an instance of a dynamic data with a specific string in a listbox. In my code below, I have the idea but I don’t know how to complete it.

Option Explicit
Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnHeads = True
        .ColumnWidths = "80;80"
        .RowSource = "Sheet9!A2:B10"
    End With
        Dim i As Integer
        Dim iCountA As Integer, iCountB As Integer, iCountC As Integer, iCountD As Integer, iCountE As Integer
        
        For i = 0 To ListBox1.ListCount - 1
            'if i string in column Color is equal to "A" then show the count as iCountA in labelA of the form
            'if i string in column Color is equal to "B" then show the count as iCountB in labelB of the form
            'if i string in column Color is equal to "C" then show the count as iCountC in labelC of the form
            'if i string in column Color is equal to "D" then show the count as iCountD in labelD of the form
            'if i string in column Color is equal to "E" then show the count as iCountE in labelE of the form
        Next
        Me.labelA.Caption = iCountA
        Me.labelB.Caption = iCountB
        Me.labelC.Caption = iCountC
        Me.labelD.Caption = iCountD
        Me.labelE.Caption = iCountE
End Sub

Thank you in advance..

Answer 1

Count Unique Items

Main

Private Sub UserForm_Initialize()
    
    Dim Ids() As Variant: Ids = VBA.Array("A", "B", "C", "D", "E")
    
    With Me
        Dim Labels() As Variant:
        Labels = VBA.Array(.labelA, .labelB, .labelC, .labelD, .labelE)
        Dim dict As Object
        With .ListBox1
            .ColumnCount = 2
            .ColumnWidths = "80;40"
            .ColumnHeads = True
            .RowSource = "Sheet1!A2:B10"
            Set dict = DictColumnCount(.List, 1) ' 2nd column
        End With
        Dim n As Long, iStr As String
        For n = 0 To UBound(Ids)
            iStr = Ids(n)
            If dict.Exists(iStr) Then
                Labels(n).Caption = iStr & ": " & dict(iStr)
            Else
                Labels(n).Caption = ""
            End If
        Next n
    End With

End Sub

The Help

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the unique values and their count from a column
'               ('ColumnIndex') of a 2D array ('Data') in the keys and items
'               of a dictionary.
' Remarks:      Error values and blanks are excluded.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DictColumnCount( _
    ByVal Data As Variant, _
    Optional ByVal ColumnIndex As Variant) _
As Object
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' case-insensitive
    
    Dim c As Long
    
    If IsMissing(ColumnIndex) Then
       c = LBound(Data, 2)
    Else
       c = CLng(ColumnIndex)
    End If
    
    Dim Key As Variant
    Dim r As Long
    
    For r = LBound(Data, 1) To UBound(Data, 1)
        Key = Data(r, c)
        If Not IsError(Key) Then
            If Len(Key) > 0 Then
                dict(Key) = dict(Key) + 1
            End If
        End If
    Next r
   
    If dict.Count = 0 Then Exit Function
    
    Set DictColumnCount = dict

End Function