none
ADD ITEMS TO COMBOBOX RRS feed

  • Question

  • using this code to add names to combobox2:

    Private Sub LOADCB()
    For Each c In Range("P621:P630")
            If c.Value <> "" Then
                ComboBox2.AddItem c.Value
               
                End If
           
               Next
    End Sub

    problem is same name could appear in range("p621:p630") more than once but I only want it to appear in the combobox once!

    any thoughts?

    thanks

    Doug

    seem to recall something from VB (NOT VBA) that went something like:

    if not combobox2.items.contains (c.value)then

    combobox2.items.add (c.value)

    • Edited by 6da4 Sunday, February 19, 2017 1:47 PM
    Sunday, February 19, 2017 1:27 PM

Answers

  • The Contains method doesn't exist in VBA. You can use a collection to store the unique values:

    Private Sub LOADCB()
        Dim c As Range
        Dim col As New Collection
        Dim v As Variant
        On Error Resume Next
        For Each c In Range("P621:P630")
            If c.Value <> "" Then
                col.Add Key:=CStr(c.Value), Item:=c.Value
            End If
        Next c
        For Each v In col
            ComboBox2.AddItem v
        Next v
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by 6da4 Sunday, February 19, 2017 3:24 PM
    Sunday, February 19, 2017 3:16 PM

All replies

  • The Contains method doesn't exist in VBA. You can use a collection to store the unique values:

    Private Sub LOADCB()
        Dim c As Range
        Dim col As New Collection
        Dim v As Variant
        On Error Resume Next
        For Each c In Range("P621:P630")
            If c.Value <> "" Then
                col.Add Key:=CStr(c.Value), Item:=c.Value
            End If
        Next c
        For Each v In col
            ComboBox2.AddItem v
        Next v
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by 6da4 Sunday, February 19, 2017 3:24 PM
    Sunday, February 19, 2017 3:16 PM
  • thanks Hans,

    very helpful!!

    Doug

    Sunday, February 19, 2017 3:26 PM