How to populate a combobox using an arraylist?

David Rojas 0
Viewed 41

I am trying to populate a combo box using an arraylist. I used the following code but it doesn't seem to work.

Private Sub UserForm_Initialize()
        Sheets("MSS").Select
            
Dim counter As Integer
Dim cmbox_opt As String
Dim array_cmbox As ArrayList
Set array_cmbox = New ArrayList
Dim last_row As Integer
Dim i As Integer
    
    
last_row = Range("B" & Rows.Count).End(xlUp).Row
    
For counter = 3 To last_row
    cmbox_opt = Range("B" & counter).Value
    If Not array_cmbox.Contains(cmbox_opt) Then
          array_cmbox.Add cmbox_opt
    End If
Next
For i = 1 To i = 1 + i
    If array_cmbox.Contains(array_cmbox(i)) Then
        Me.ComboBoxArea.AddItem array_cmbox(i)
    End If
Next
    
    
    
        'Ensure Window Dimensions'
        With Me
            .Width = 577
            .Caption = "Master Sanitation Schedule Form v.1.0"
            .Height = 274
        End With

End Sub

However, if I just add the line

Me.ComboBoxArea.AddItem array_cmbox(2)

It does add the second item to the combo box from the ArrayList

arraylist vba excel
1 Answers

Populate a Combo Box Using an Array List

Private Sub UserForm_Initialize()
        
    PopulateComboBoxArea
    
    'Ensure Window Dimensions'
    With Me
        .Width = 577
        .Caption = "Master Sanitation Schedule Form v.1.0"
        .Height = 274
    End With

End Sub

Private Sub PopulateComboBoxArea()
    
    ' Define constants.
    Const wsName As String = "MSS"
    Const FirstRow As Long = 3
    Const Col As String = "B"
        
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    ' Calculate the last row ('LastRow'),
    ' the row of the last non-empty cell in the column.
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    
    ' Validate the last row.
    If FirstRow > LastRow Then Exit Sub
    
    ' Reference the one-column range ('crg').
    Dim crg As Range
    Set crg = ws.Range(ws.Cells(FirstRow, Col), ws.Cells(LastRow, Col))
        
    ' Write the number of rows to a variable ('rCount').
    Dim rCount As Long: rCount = crg.Rows.Count
        
    ' Write the values from the (one-column) range
    ' to a 2D one-based (one-column) array ('cData').
    Dim cData() As Variant
    If rCount = 1 Then ' one cell
        ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
    Else ' multiple cells
        cData = crg.Value
    End If
    
    ' Create and reference a new array list.
    ' Either early binding: needs a reference to 'mscorlib.dll'...
    'Dim arl As ArrayList: Set arl = New ArrayList
    ' ... or late binding: no reference needed
    Dim arl As Object: Set arl = CreateObject("System.Collections.ArrayList")
    ' IMO, this would be the way to go since when using early binding,
    ' the Intellisense, that would be useful to easily learn how to use
    ' the array list, doesn't work at this moment.
    
    ' Declare additional variables.
    Dim r As Long ' Current Array Row
    Dim cString As String ' Current Array Value Converted to a String
    
    ' Loop through the rows of the array...
    For r = 1 To rCount
        ' Write the value converted to a string to a variable ('cString').
        cString = CStr(cData(r, 1))
        ' Check if the string doesn't exist in the array list.
        If Not arl.Contains(cString) Then ' string is not in the array list...
            arl.Add cString ' ... so add it
        'Else ' string is already in the array list; do nothing
        End If
    Next r
    
    ' IMO, a huge disadvantage, compared to the dictionary, is that
    ' you cannot compare the strings case-insensitively when using 'Contains'.
    ' When using a dictionary you could simply use
    ' e.g. dict.CompareMode = vbTextCompare' making e.g. 'A = a'.
    
    ' IMO, one of the very few reasons to use an array list over a dictionary
    ' is that you can easily sort it:
    arl.Sort
    
    ' Populate the combo box with the values from the array list
    ' (no need to loop).
    Me.ComboBoxArea.List = arl.ToArray

End Sub
VBasic20080
Related