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]

You know what : don't bother with the hidding of combobox if too complicated : we can place the combobox in the cells AK2 : AK6 and AL2:AL6.

Ok, this is complicated indeed.?

So we now deal with the dynamic range of the list problem..
Replace "Sub allList(a As Long)" with this:

Sub allList(a As Long)
Dim fm, gm
Dim n As Variant

With Sheets("Matrice BXL")
    fm = Application.Match("Sélectionner paquet sol", .Range("D:D"), 0)
    gm = Application.Match("Sélectionner paquet eau", .Range("D:D"), 0)
            
    n = .Range("D:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
        If a = 1 Then
            'LIST 1 location
            vList1 = .Range("D" & fm & ":D" & gm - 1)
        ElseIf a = 2 Then
            'LIST 2 location
             vList2 = .Range("D" & gm & ":D" & n)
        End If

End With
End Sub

The code will find "Sélectionner paquet sol" & "Sélectionner paquet eau" in col D in sheet Matrice BXL.
LIST1 will start from "Sélectionner paquet sol" to 1 cell above "Sélectionner paquet eau".
LIST2 will start from "Sélectionner paquet eau" to the last cell with value in col D.
Please try it by adding or removing some rows in the range in question.

About the bilingual data validation problem:
Sorry, I still can't find what cause the problem, still trying though.

 

[jondu]

Please try it by adding or removing some rows in the range in question.

Ok, it's works, perfect ! And as decided, I moved the combobox to AK2:AK6 and AL2:AL6 and no more problems with hiding/unhiding.

About the bilingual data validation problem:
Sorry, I still can't find what cause the problem, still trying though.

Ok, it's less important.

The very last thing : when the user select manually a value in Y261:Y265 and Y274:Y278 (with the menu from the menu list) : there is a movement and the view goes direct to the top of the sheet.
Once he selects a value with help of the combobox, then there is no more problem (but you need to use at list one time a combobox to have the correct behavior).

 

[jondu]

And for the bilingual menus : I have to say that the code was made originally to work with the 2 cells in green. Then I decided then to add other menu-cells (the ones in yellow) and it is therefore normal that the bilingual behavior do not work for those yellow cells. But I still don't know how to manage that. Maybe this info over the chronological history can help you.

 

[Akuini]

Actually there's a problem with List1 range, it should be D18:D142 but the last code will give you D18:D163.
Well, I need something to define the last row of the range. Maybe the word "EAU" in A149? or "Bouteilles" in D153? or anything else?

The very last thing : when the user select manually a value in Y261:Y265 and Y274:Y278 (with the menu from the menu list) : there is a movement and the view goes direct to the top of the sheet.

Try this one:
Just replace all code in the module with this, it's long so I upload it in /pastebin.com, just click the download button:

Option Explicit'Option Explicit'========================================== - Pastebin.com

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

pastebin.com

 

[jondu]

ok perfect. There was something wrong with combobox 6

I replace this code : If ComboBox6.Value = vbNullString Then ComboBox6.List = vList2
By this one : ComboBox6.List = vList2
It's now OK.

Actually there's a problem with List1 range, it should be D18:D142 but the last code will give you D18:D163.
Well, I need something to define the last row of the range. Maybe the word "EAU" in A149? or "Bouteilles" in D153? or anything else?

From now, I moved the list 'Bouteilles' in another column => from D to E. So the list 'SOL' is now OK, but have just some blank lines at the end (not a big deal).
Anyway I can add a text like 'END SOL' in D142. That would be the best. And I rembember that when I add rows, I insert it above 'END SOL'.
We then need to adapt a little bit the code I guess :
- the list1 SOL must start at "Sélectionner paquet sol" and end at "END SOL"
- the list2 EAU must start at "Sélectionner paquet eau" and stop at the last cell with value.

 

[Akuini]

I replace this code : If ComboBox6.Value = vbNullString Then ComboBox6.List = vList2
By this one : ComboBox6.List = vList2
It's now OK.

Ah, you're right.

We then need to adapt a little bit the code I guess :
- the list1 SOL must start at "Sélectionner paquet sol" and end at "END SOL"
- the list2 EAU must start at "Sélectionner paquet eau" and stop at the last cell with value.

Ok, use this code for Sub allList(a As Long):

Sub allList(a As Long)
Dim fm, fm1, gm
Dim n As Variant

With Sheets("Matrice BXL")
    fm = Application.Match("Sélectionner paquet sol", .Range("D:D"), 0)
    fm1 = Application.Match("END SOL", .Range("D:D"), 0)
    gm = Application.Match("Sélectionner paquet eau", .Range("D:D"), 0)
            
    n = .Range("D:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
        If a = 1 Then
            'LIST 1 location
            vList1 = .Range("D" & fm & ":D" & fm1)
        ElseIf a = 2 Then
            'LIST 2 location
             vList2 = .Range("D" & gm & ":D" & n)
        End If

End With
End Sub

 

[jondu]

ok, perfect.
And now that I see the efficiency, if it's possible to configure the code also to be able to add a 'END EAU' in list2 it would be perfect. So I can say when I stop the list, as for List1 (i.e. not showing the 15 last '-' in list2 EAU).
We are finnish after that !
And I have to pay you a big drink

 

[jondu]

Note that when I try with this, I get an error.

Sub allList(a As Long)
    If a = 1 Then
            '>>> LIST 1 location
                vList1 = Sheets("Matrice BXL").Range("D18:D132")
    ElseIf a = 2 Then
           '>>> LIST 2 location
                vList1 = Sheets("Matrice BXL").Range("D164:D261")
    End If
End Sub

 

[Akuini]

           '>>> LIST 2 location
                vList1 = Sheets("Matrice BXL").Range("D164:D261")

That should be vList2

if it's possible to configure the code also to be able to add a 'END EAU' in list2 it would be perfect. So I can say when I stop the list, as for List1 (i.e. not showing the 15 last '-' in list2 EAU).

Try this:

Sub allList(a As Long)
Dim fm, fm1, gm, gm1
Dim n As Variant

With Sheets("Matrice BXL")
    fm = Application.Match("Sélectionner paquet sol", .Range("D:D"), 0)
    fm1 = Application.Match("END SOL", .Range("D:D"), 0)
    gm = Application.Match("Sélectionner paquet eau", .Range("D:D"), 0)
    gm1 = Application.Match("END EAU", .Range("D:D"), 0)
    
'    n = .Range("D:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
        If a = 1 Then
            'LIST 1 location
            vList1 = .Range("D" & fm & ":D" & fm1)
        ElseIf a = 2 Then
            'LIST 2 location
             vList2 = .Range("D" & gm & ":D" & gm1)
        End If

End With
End Sub

 

[jondu]

OK, eveything is done and I have adapted all my documents (other similar documents, with other data for other regions).
We can close this thread I think.
I would like to thank you warmly for this work of several days. I am very impressed, but especially very surprised to see that people can so help.
Let me know if you come to belgium !
Thank you so much.