Prevent Combobox Duplicates

cbanks · Jul 16th 2007

How do i stop my combobox from displaying duplicate entries?

See Sort & Remove Duplicates From ListBox

**mikerickson** · Jul 16th 2007

Re: Combobox Duplicates

Don't put them in. Here's one way to load a combobox with the unique entries in dataRRay.

Code

Dim dataRRay1 As Variant
Dim tempColl As New Collection
Dim xVal As Variant


On Error Resume Next
For Each xVal In dataRRay1
    tempColl.Add Item:=xVal, key:=CStr(xVal)
Next xVal
On Error GoTo 0


With UserForm1.ComboBox1
    .Clear
    For Each xVal In tempColl
        .AddItem xVal
    Next xVal
End With
Set mycoll = Nothing

Display More

cbanks · Jul 16th 2007

Re: Combobox Duplicates

Okay, I dony understand. My combobox is setup with code below

Code

Private Sub UserForm_Initialize()
    Set MyData = Sheet1.Range("a1").CurrentRegion   'database
    With Me
        .Caption = "Database Example"    'userform caption
    End With
    ComboBox1.List = WorksheetFunction.Transpose(Sheets("Sheet1").Range("A2:A65536"))




End Sub

Display More

cbanks · Jul 16th 2007

Re: Combobox Duplicates

Ok, I have spent a great deal of time manipulating one of roy's database forms to make this work. however, for some reason it does not search correctly. it picks up the county right, but not the state.. i need it to search by state and county.. both being criteria. not just county. thanks for your help

**Kenneth Hobson** · Jul 16th 2007

Re: Combobox Duplicates

Replace your ComboBox1.List line with a routine as mikerickson suggested to add only unique items to your combobox1. Out the 2nd Sub in a Module and use the first to set it up and call it. Notice how I made use of one means to set a range.

This method is similar but makes it a bit more modular.

Code

Private Sub UserForm_Initialize()
  Dim sRange As Range
  Set sRange = Range("A2", Range("A65536").End(xlUp))
  FillCtrlUnique sRange, Me.ComboBox1
End Sub




Sub FillCtrlUnique(myRange As Range, myControl As Control)
  Dim Coll As New Collection
  Dim var As Variant
  Dim cell As Range
   
  On Error Resume Next
  For Each cell In myRange
      Coll.Add Item:=cell.Value, key:=CStr(cell.Value)
  Next cell
  On Error GoTo 0
   
  With myControl
      .Clear
      For Each var In Coll
          .AddItem var
      Next var
  End With
  Set Coll = Nothing
End Sub

Display More

cbanks · Jul 16th 2007

Re: Combobox Duplicates

Thanks. I will do that.. Im trying to use .find to locate information based on the criteria from 2 combobox'es. i have made an attempt, but its not working. It only picks up data based on what is entered in comboxbox2. Can you help?

Code

Private Sub cmbFindAll_Click()
    Dim FirstAddress As String
    Dim strFind As String, strfind2 As String   'what to find
    Dim rSearch, rsearch2 As Range    'range to search
    Dim fndA, fndB, fndC, fndD, fnde, fndf, fndg, fndh, fndi As String
    Dim head1, head2, head3, head4, head5, head6, head7, head8, head9 As String 'heading s for list
    Dim i As Integer
    i = 1
    Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp))
     Set rsearch2 = Sheet1.Range("b2", Range("b65536").End(xlUp))
    strFind = Me.ComboBox1.Value
    strfind2 = Me.ComboBox2.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        End With
             With rsearch2
                Set c = .Find(strfind2, LookIn:=xlValues)
                    If Not c Is Nothing Then
'    With rSearch
'        Set c = .Find(strFind, LookIn:=xlValues)
'        End With
'        With rsearch2
'        Set c = .Find(strfind2, LookIn:=xlValues)
'        If Not c Is Nothing Then    'found it
            c.Select
            'load the headings
            head1 = Range("a1").Value
            head2 = Range("b1").Value
            head3 = Range("c1").Value
            head4 = Range("d1").Value
            head5 = Range("e1").Value
            head6 = Range("f1").Value
            head7 = Range("g1").Value
            head8 = Range("h1").Value
            head9 = Range("i1").Value
            
            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head4
                MyArray(0, 5) = head5
                MyArray(0, 6) = head6
                MyArray(0, 7) = head7
                MyArray(0, 8) = head8
                MyArray(0, 9) = head9
                
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                
                fndA = ComboBox1.Value
                fndB = ComboBox2.Value
                fndC = c.Offset(0, 1).Value
                fndD = c.Offset(0, 2).Value
                fnde = c.Offset(0, 3).Value
                fndf = c.Offset(0, 4).Value
                fndg = c.Offset(0, 5).Value
                fndh = c.Offset(0, 6).Value
                fndi = c.Offset(0, 7).Value


                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fnde
                MyArray(i, 5) = fndf
                MyArray(i, 6) = fndg
                MyArray(i, 7) = fndh
                MyArray(i, 8) = fndi
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress


        End If
    End With
    'Load data into LISTBOX
    Me.ListBox1.List() = MyArray


End Sub

Display More

**ComboBox worked like a charm. thanks.

**Kenneth Hobson** · Jul 16th 2007

Re: Combobox Duplicates

Might take a while to wrap my head around that one.

Suggestions:
1. Set rSearch as type Range, not Variant.
2. Try searching by rSearch and within that loop, search by rSearch2. You will need 2 c's and may want to use fewer With()s.

You could set a discontiguous Range by the found items in rSearch. You could then use the OffSet() method to get the Range for a 2nd find loop.

Another method scribbled out for one comparison.
e.g.

Code

If [A1]&[B1]=ComboBox1.text & ComboBox2.text then...

The ... means to assign that row to a found range.

cbanks · Jul 16th 2007

Re: Combobox Duplicates

im sorry, but my skills are not developed enough to understand what you are saying. I have previously posted workbook examples, but i just need the segment below fixed? is that not possible, or am i goin to have to try another route?

**Kenneth Hobson** · Jul 16th 2007

Re: Combobox Duplicates

For (1), you have:

Code

Dim rSearch, rsearch2 As Range

This is the same as:

Code

Dim rSearch as Variant, rsearch2 As Range

I think you meant:

Code

Dim rSearch as Range, rsearch2 As Range

Your find type strings are coded as above too. In other words, if you do not set an As type, the type is set as Variant.

I will look into item (2), later tonight.

cbanks · Jul 16th 2007

Re: Combobox Duplicates

yea, im using on example roy put together on his site.. below is the code i am using.. i would like to add another combobox that i could use as search criteria.. basicly if combobox1 was found in col a2, then if combobox2 was found in b2 then its a match and all is good.. but now what it does when i add the combobox2 is just search for combobox2 disregarding combobox1.. anyhow.. here is the code im using with one combobox.. its very limited.. if someone would just show me how to use 2 that would be awesome. thanks.

Code

Private Sub cmbFindAll_Click()
    Dim FirstAddress As String
    Dim strFind As String    'what to find
    Dim rSearch As Range     'range to search
    Dim fndA, fndB, fndC, fndD, fnde, fndf, fndg, fndh As String
    Dim head1, head2, head3, head4, head5, head6, head7, head8 As String 'heading s for list
    Dim i As Integer
    i = 1
    Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp))
    strFind = Me.ComboBox1.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            'load the headings
            head1 = Range("a1").Value
            head2 = Range("b1").Value
            head3 = Range("c1").Value
            head4 = Range("d1").Value
            head5 = Range("e1").Value
            head6 = Range("f1").Value
            head7 = Range("g1").Value
            head8 = Range("h1").Value
            
            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3
                MyArray(0, 3) = head4
                MyArray(0, 4) = head5
                MyArray(0, 5) = head6
                MyArray(0, 6) = head7
                MyArray(0, 7) = head8
                
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 3).Value
                fnde = c.Offset(0, 4).Value
                fndf = c.Offset(0, 5).Value
                fndg = c.Offset(0, 6).Value
                fndh = c.Offset(0, 7).Value
                


                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fnde
                MyArray(i, 5) = fndf
                MyArray(i, 6) = fndg
                MyArray(i, 7) = fndh
                
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress


        End If
    End With
    'Load data into LISTBOX
    Me.ListBox1.List() = MyArray
   'Me.Height = 318
End Sub

Display More

**Kenneth Hobson** · Jul 16th 2007

Re: Combobox Duplicates

Try this:

Code

Private Sub ComboBox2_Enter()
  Dim Coll As New Collection
  Dim var As Variant
  Dim cell As Range, myRange As Range
  
  Set myRange = Range("B2", Range("B65536").End(xlUp))
  
  On Error Resume Next
  For Each cell In myRange
      If cell.Offset(0, -1) = ComboBox1.Text Then
        Coll.Add Item:=cell.Value, key:=CStr(cell.Value)
      End If
  Next cell
  On Error GoTo 0
   
  With ComboBox2
      .Clear
      For Each var In Coll
          .AddItem var
      Next var
  End With
  Set Coll = Nothing
End Sub

Display More

Dave Hawley · Jul 17th 2007

Re: Combobox Duplicates

See Sort & Remove Duplicates From ListBox

cbanks · Jul 17th 2007

Re: Prevent Combobox Duplicates

ok, but you left off adding the information to the list box? how do i do that now?

cbanks · Jul 17th 2007

Re: Prevent Combobox Duplicates

The suggestion worked as it limited the amount of options allowed in combobox2, but it still does not apply this criteria to the search.. its still on search via combobox1 and is not taking combobox2 into consideration? my question is how do i get the code i posted earlier to search by both combobox'es

Dave Hawley · Jul 18th 2007

Re: Prevent Combobox Duplicates

Quote from cbanks

ok, but you left off adding the information to the list box? how do i do that now?

No I didn't.

Prevent Combobox Duplicates

Participate now!

Share