Checking if duplicate element in combobox

[dinosaur_uk]

Hi There,

I have been populating my combobox using this method, is there a faster way of asking the combobox if an element is duplicate? and if it is not to add it in. I know in VB.Net there is a lstbox.items.contains("BAH") as boolean, but is there a similar method in VBA?

Cheers

Ken

Private Sub UserForm_Initialize()
'We need to load up the combo box with the class of chemical
    Dim i As Integer
    Dim j As Integer
    Dim str As String
    Dim Unique As Boolean
    Unique = True

    Me.cboChemical.Clear

    'We need to add the option of all items
    Me.cboChemical.AddItem ("List All")

    For i = 9 To 88
        str = Sheets("Solvent").Cells(i, 1)
        If str = "" Then
            'Do nothing
        Else
            'Probably a more elegant way of doing this
            Unique = True
            For j = 0 To Me.cboChemical.ListCount - 1
                If str = Me.cboChemical.List(j) Then
                    'it already exists on the list, exit
                    Unique = False
                End If
            Next
            If Unique = True Then
                Me.cboChemical.AddItem (str)
            End If

        End If
    Next
End Sub

[Lord Orwell]

I don't know about simpler, but assigning it to an array and then loading the combobox with the list property will definitely be a lot faster.

[dinosaur_uk]

how do you assign it to an array?

[westconn1]

in vb you can use the sendmessage API with cbofindexactstring to check for an item in a combobox, but i don't know if it will work with forms 2.0 control combobox used in excel

do a search in the classic vb forum then see if it will work

[dinosaur_uk]

thanks for your reply

hmm. Is it possible to feed a control into a subroutine and get it to return a value? If it so, how do i do that?

[Lord Orwell]

to set a combobox from an array, assign the list or column property to the array name. You load the array to start with exactly like the code you used there, just replace your combo reference with the array.

[dinosaur_uk]

and how do i do that? sorry, i am abit rusty

[Lord Orwell]

which part? combo1.list = myarray()
or the filling in the array part?

[westconn1]

you can use the matchfound property of the combobox to know if the item already exists

try like this

For i = 9 To 88
        Me.cboChemical.Text = Sheets("Solvent").Cells(i, 1)
         If Not Me.cboChemical.MatchFound And Not Me.cboChemical.Text Then Me.cboChemical.AddItem Me.cboChemical.Text
         End If  
    Next

[zaza]

If your combobox is on the worksheet itself, populate it using the ListFillRange property, which is just a string corresponding to a cell range.
If your combobox is on a userform, use the RowSource property, which does the same thing.

Then you can check your Excel range for duplicates and get rid of them, which will be much easier. One way, if you don't mind sorting, is to sort your list and then you only have to check each item against the next one.
Or you could use Find.
Or, you could use the following:

Range("A1:A10").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Obviously you need to set the range, and you may not want to FilterInPlace. Also, you need to have a header on the column. It's the same as using the Filter option from the Data menu.

Personally, I would do this latter option, then set your combobox to populate from whatever range is left - you could just do a Find to get the first blank cell, for example.


A few ideas, anyway.

zaza

[dinosaur_uk]

you can use the matchfound property of the combobox to know if the item already exists

try like this

For i = 9 To 88
        Me.cboChemical.Text = Sheets("Solvent").Cells(i, 1)
         If Not Me.cboChemical.MatchFound And Not Me.cboChemical.Text Then Me.cboChemical.AddItem Me.cboChemical.Text
         End If  
    Next

Hiya,
the code does not work

[westconn1]

the code does not work

i did test it (with some alterations) before posting, what part does not work

edit: a couple of copy errors

For i = 9 To 88
              Me.cboChemical.Text = Sheets("Solvent").Cells(i, 1)
               If Not Me.cboChemical.MatchFound And Not Me.cboChemical.Text = "" Then Me.cboChemical.AddItem Me.cboChemical.Text
          Next