How to know if a combobox has an item?-CodePudding

When I activate a sheet, I want to populate the items of a combobox with the items of a column, but I want to avoid duplications. It is for filter purposes.

Well, my first idea is to iterate all the rows with data to see if the item is in the combobox and if it is not in the combobox, then add it.

However, I don't know how to check if the item is in the combobox or not, or if there is an easier way instead of iterate over all the rows.

Thanks.

EDIT: I add the code that I am using to populate the combobox

Private Sub Worksheet_Activate()
    
    Me.cmbProveedores.Clear
    Me.cmbProveedores.AddItem ("")
    
    'Se recorren todas las filas de la hoja
    For miIterador = PrimeraLineaConDatos To 65536
    
        If (Me.Cells(miIterador, NombreColumnaRevisiones) = "") Then Exit For
    
    
        Dim miProveedor As String
        miProveedor = Me.Cells(miIterador, NombreColumnaProveedor)
    
        If miProveedor <> "" Then
            Me.cmbProveedores.Value = miProveedor
            If Me.cmbProveedores.MatchFound = False Then
                Me.cmbProveedores.AddItem (miProveedor)
            End If
        End If

    Next miIterador
    
    
    Me.cmbProveedores.Value = ""
End Sub

I have too variables:

NombreColumnaRevisiones: I use this variable to know when to stop to iterate the rows. I stop when I find the first cell that is empty in this column.
NombreColumnaProveedor: is the column from which I get the items for the combobox.

CodePudding user response：

Populate Combo Box With Unique Values From a Column (Dictionary)

Option Explicit

Private Sub Worksheet_Activate()
    
    ' Reference the range.
    
    Dim lr As Long
    lr = Me.Cells(Me.Rows.Count, NombreColumnaProveedor).End(xlUp).Row
    
    Dim rCount As Long: rCount = lr - PrimeraLineaConDatos   1
    If rCount < 1 Then Exit Sub
    
    Dim rg As Range: Set rg = Me.Cells(PrimeraLineaConDatos, _
        NombreColumnaProveedor).Resize(rCount)
    
    ' Write the values from the range to an array.
    
    Dim Data As Variant
    
    If rCount = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    ' Write the unique values from the array to a dictionary.
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim Key As Variant
    Dim r As Long
    
    For r = 1 To rCount
        Key = Data(r, 1)
        If Not IsError(Key) Then
            If Len(Key) > 0 Then
                dict(Key) = Empty
            End If
        End If
    Next r
    If dict.Count = 0 Then Exit Sub
    
    ' Populate the combo box.
    
    Me.cmbProveedores.Clear
    Me.cmbProveedores.List = dict.Keys
    
End Sub