searchable dropdown list with exchangeable names

[jondu]

Hello,
I did a list as suggested here :
https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/
It works very fine.

[FONT=&quot]However, I would like to improve it for my needs.
My list in column F contains cell with many words, separated by a coma : “,”
Example :
F3 : house, cat, hospital, game
F4 : house, game, hospital
F5 : hospital, cat, game, house
etc.[/FONT]
[FONT=&quot]I would like to be able to find the cell depending the names I’m typing in the B3 combobox.
Example, I type this :
house, cat
OR
cat, house
=> I must have as option in the dropdown list the F3 and F5 cells.
(the order of the names must be able to be exchanged)
Is it possible ? (and without vba preferred)[/FONT]

 

[Akuini]

Is it possible ? (and without vba preferred)

Are still looking for a solution?
I might have an idea but it uses vba (no formula).
Let me know if you're interested.

 

[jondu]

Hello Akuini,
Yes I'm still interested !

 

[Akuini]

Ok, here's an example:

Private Sub ComboBox1_Change()

Dim d As Object, vList, i As Long
Dim z, x
[COLOR=#0000cd]vList = Range("F2", Cells(Rows.Count, "F").End(xlUp)).Value[/COLOR]
   
With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, vList, 0)) Then
        Set d = CreateObject("scripting.dictionary")
        
        For i = LBound(vList) To UBound(vList)
              d(vList(i, 1)) = Empty
        Next
        
        For Each x In Split(ComboBox1.Value, ",")
            x = Trim(x)
            For Each z In d.keys
                If InStr(1, z, x, vbTextCompare) = 0 Then d.Remove z
            Next
        Next
    
           .List = d.keys
           .DropDown
    
    ElseIf .Value <> "" Then
        
        [COLOR=#0000cd]Range("B3") = ComboBox1.Value[/COLOR]
        
    ElseIf .Value = vbNullString Then
           
           ComboBox1.List = vList
    
    End If

End With

End Sub


Private Sub ComboBox1_GotFocus()
    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    'ComboBox1.ListRows = 10 'to show how many item
End Sub

Private Sub ComboBox1_DropButt*******()
Dim vList
    If ComboBox1.Value = vbNullString Then
           [COLOR=#0000cd]ComboBox1.List = Range("F2", Cells(Rows.Count, "F").End(xlUp)).Value[/COLOR]
    End If
End Sub

The workbook:

https://www.dropbox.com/s/lbzqqmm6p...le-dropdown-list-exchangeable-names.xlsm?dl=0

You can find the code in sheet1 code module.
How to set up:
1. Insert an activex combobox in the sheet in question, its name must be "combobox1".
2. Copy the code to the sheet's code module.
3. To change the cell that links to the combobox, change this line to suit:

Range("B3") = ComboBox1.Value

4. To change the range where the list is, change these 2 lines to suit:

vList = Range("F2", Cells(Rows.Count, "F").End(xlUp)).Value

ComboBox1.List = Range("F2", Cells(Rows.Count, "F").End(xlUp)).Value

How to use:
1. Type the keywords separated by comma or comma+space.
2. The search is partial-match, so for example to search "cat,house" you can just type "ca,hou" or "hou,ca".

 

[jondu]

Dear Akuini,

This works great !!
And the partial match is very interesting for my application !
Thank you for the very clear explanations !

In your copied code ther is ;
Private Sub Combox1_DropButt*******()

But I could manage that with your example sheet.

I'm always so impresed how unkown people can help others !

Thank you so much !

 

[jondu]

And sorry one more thing : what if the list of names is in another sheet that the combobox ?

 

[jondu]

And also :
What if I have different combobox ?
There are actually 10 combobox on sheet 1 :

5 differents combobox searching in the list1, located on sheet 2

5 differents combobox searching in the list2, located on sheet3

 

[Akuini]

And sorry one more thing : what if the list of names is in another sheet that the combobox ?

Try this (change ("Sheet2") to suit):

vList = Sheets("Sheet2").Range("F2", Sheets("Sheet2").Cells(Rows.Count, "F").End(xlUp)).Value

ComboBox1.List = Sheets("Sheet2").Range("F2", Sheets("Sheet2").Cells(Rows.Count, "F").End(xlUp)).Value

what if I have not 1, but 5 differents combobox

the 5 differents combobox are in the same sheet?

 

[Akuini]

And also :
What if I have different combobox ?
There are actually 10 combobox on sheet 1 :

5 differents combobox searching in the list1, located on sheet 2

5 differents combobox searching in the list2, located on sheet3

Ok, I need to modify the code.
I'll get back later.

The 5 differents combobox, each links to different cell?

 

[Akuini]

Do you really need 10 combobox or it can be more in the future?
Could you explain what you're trying to do with the combobox?

You may consider this approach:
I created a searchable combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type keyword in the combobox and the list will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.
You can find an example here:
https://www.mrexcel.com/forum/excel...auto-complete-functionality-cells-column.html