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]

 

[jondu]

Thank you once again for the quick reply !
I have a special training day for the work today (no computer), I'll answer you tonight !

But in short already :
- Yes I have only 2x5 combobox (this number will not change in the future)
- I need well 10 differents combobox
- the 10 combobox are located in the same sheet (=sheet 1) (5 of them are linked to a list in sheet2 and 5 of them are linked to a list in sheet 3).

It's to find analysis package, containing different parameters (the list of name are actually a list of chemicals parameters).
I must be able to select different package on sheet 1 (= the 10 combobox).
Once the package is selected in a combobox, I have a price and description etc in others cells on the same sheet 1.
The analysis package is quite long.

 

[jondu]

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

Ok this works perfectly

 

[jondu]

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

Ok thank you.
In this case, this unique dynamic combobox (appearing and disapearing) could to the job indeed.
But in comparaison with this last document "deCombobox - dinamically visible, searchable, unique, sort, arraylist, on-off" I do need the following adaptations :

1. The combobox(s) must refers to 2 different lists (2 different comboboxes ?). For remember, those 2 lists area located in 2 different sheets (sheet2 and sheet3).
2. The suggested list (displayed in the combobox in sheet1) must not to be sorted alphabetically (this must respect the specific order list from sheet2 or sheet3)
3. The suggested list (displayed in the combobox in sheet1) must be able to contains blanks (the lists are separated in sections, with titles for some groups in CAPITALS and surrounded by 2 blanks rows for the visibility (in case of the user chooses to select manually = important for some reasons)
4. The words must be "exchengeable" (ex : to find "Birmingham, Alabama" : the user can type "Alabama, Birmingham")
5. The partial match must be fully active (ex : to find "Birmingham, Alabama" : the user can type "Bir, Alab")
6. If the solution of dynamic appearing combobox is chosen, the button ON-OFF is appropriated in this case (= button wanted)

Note : Points 2, 3, 4, 5 where already filled in the first version of you document/code.

And to remind/to be clear, the need is :

Searchable cell1 (in sheet1) => referring to list1, on sheet2
Searchable cell2 (in sheet1) => referring to list1, on sheet2
Searchable cell3 (in sheet1) => referring to list1, on sheet2
Searchable cell4 (in sheet1) => referring to list1, on sheet2
Searchable cell5 (in sheet1) => referring to list1, on sheet2

And then a little lower :
Searchable cell6 (in sheet1) => referring to list2, on sheet3
Searchable cell7 (in sheet1) => referring to list2, on sheet3
Searchable cell8 (in sheet1) => referring to list2, on sheet3
Searchable cell9 (in sheet1) => referring to list2, on sheet3
Searchable cell10 (in sheet1) => referring to list2, on sheet3

Could you gather this requests ?
Once again, big thank you in advance, it is already very impressive !

 

[jondu]

And also : it is possible that the searchable cells change from row by inserting rows before the searchable cells
Example

A1 : Searchable cell1
A2 : Searchable cell2
Etc...

Becomes (after insertion of a row before row 1)

A2 : Searchable cell1
A3 : Searchable cell2
Etc...

(I guess we need to name the searchable cells, and refer to it in the VBA code).

 

[Akuini]

I think, with your requirements above, the dynamic combobox will be hard to implement. So we'll be using 10 combobox instead.
Here's an example with only 4 combobox: ComboBox1, ComboBox2, ComboBox6, & ComboBox7. You can add the rest of the combobox and then add & modify the code by using the 4 combobox as example.

The setting:
1. LIST 1 is in sheet2, start at A2 downward, LIST 2 is in sheet3, start at A2 downward. You can change the range in Sub allList(a As Long).
2. I put some comments in the code, the ones with '>>> are where you may want to amend the code to suit.
3. ComboBox1 & ComboBox2 list source is LIST 1, and the linked cell is cell B3 (the linked cell is where the value from combobox will be transfered to).
4. ComboBox6 & ComboBox7 list source is LIST 2, and the linked cell is cell E3 .
5. There are 3 Sub for each combobox, i.e (each with different combobox names of course):
Private Sub ComboBox1_GotFocus()
Private Sub ComboBox1_Change()
Private Sub ComboBox1_DropButt*******()

First, try the example workbook, see if it works as expected.
If it works then here's how you can set it up:
1. Amend the code in the 4 combobox to suit & also in Sub allList.
2. To change the linked cell of each combobox, you need to change this line (in each Private Sub ComboBox_GotFocus()):
sLinkedCell = "B3" '>>> the cell that links to this combobox
3. Try it and make sure it works.
4. Insert another combobox to the sheet as needed. For example:

You insert a combobox & name it ComboBox3. Because you need LIST 1 as the source then copy the three Private Sub ComboBox1 that I mentioned above, paste it below the existing code, then amend it accordingly. Basically you just need to change "ComboBox1" to "ComboBox3" (you can do this by using replace all (Ctrl+H) in SELECTED TEXT).

You insert a combobox & name it ComboBox8. Because you need LIST 2 as the source then copy the three Private Sub ComboBox6 that I mentioned above, paste it below the existing code, then amend it accordingly. Basically you just need to change "ComboBox6" to "ComboBox8".

Note:
About the blank cells/items, I don't quite understand it, when the combobox is blank & you click the dropdown button then the list will appear including the blank items, but after you type a keyword do you still want the blank items in the list?
Could you explain in more detail using an example?

The workbook:
https://www.dropbox.com/s/q5yj4kct0...-dropdown-list-exchangeable-names-1.xlsm?dl=0

 

[jondu]

Dear Akuini,
The goal is almost reached.
I tried to implemente your code in a new file, and it works.
But when I copy the code to my personnal file (my working file), there is a bug.
It's says 'Erreur de compilation : variable non définie' (French) and indicates the "vList1".
I did however specify the location in the part above :
With Sheets("Sheet2")
and
With Sheets("Sheet3")

I tried with this 2 options :
- changing the name of the sheet + the name of the sheet in the code (to suit the new defined name)
- creating 2 sheets called 'Sheet2' and 'Sheet3' and not touching the code
But with inconclusive results.

It works for a new file, so the reason must come from the structure of my working file.
Here is the already set code in this file :

Option Explicit
'pour réinitialiser la macro
Private Sub CommandButton1_Click()
Application.EnableEvents = True
End Sub


'fonction pour masquer les lignes en fonction du type choisi. Les lignes ont reçu des noms, pour que ajout lignes possible
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("data1:data6")) Is Nothing Then
      Cells.EntireRow.Hidden = False
      Select Case Range("data1")
         Case "RECONNAISSANCE DE L'ÉTAT DU SOL (RES)"
            Range("E_RES_00,E_ED_00:E_TV_11,D_01:M_06,L_S_13").EntireRow.Hidden = True
         Case "ÉTUDE DÉTAILLÉE (ED)"
            Range("E_RES_00:E_ED_00,E_ER_00:E_TV_11,D_01:M_06,L_S_13").EntireRow.Hidden = True
         Case "ÉTUDE DE RISQUE (ER)"
            Range("E_RES_00:E_ER_00,E_RES_ED_ER_00:E_TV_11,D_01:M_06,L_S_13").EntireRow.Hidden = True
         Case "RECONNAISSANCE + ÉTUDE DÉTAILLÉE (RES-ED)"
            Range("E_RES_00,E_ED_00:E_TV_11,D_01:M_06,L_S_13").EntireRow.Hidden = True
         Case "ÉTUDE DÉTAILLÉE + ÉTUDE DE RISQUE (ED-ER)"
            Range("E_RES_00:E_ER_00,E_RES_ED_ER_00:E_TV_11,D_01:M_06,L_S_13").EntireRow.Hidden = True
         Case "RECONNAISSANCE + ÉTUDE DÉTAILLÉE + ÉTUDE DE RISQUE (RES-ED-ER)"
            Range("E_RES_00:E_RES_ED_ER_00,E_PA_PGR_00:E_TV_11,D_01:M_06,L_S_13").EntireRow.Hidden = True
         Case "PROJET DE GESTION DE RISQUE (PGR)"
            Range("E_RES_00:E_PA_PGR_00,E_PA_PGR_12:E_PA_PGR_17,E_SUIVI_00:E_TV_11,L_S_13").EntireRow.Hidden = True
         Case "PROJET DE GESTION DE RISQUE (PGR) + SUIVI"
            Range("E_RES_00:E_PA_PGR_00,E_SUIVI_00:E_TV_11,L_S_13").EntireRow.Hidden = True
         Case "PROJET D'ASSAINISSEMENT (PA)"
            Range("E_RES_00:E_PA_PGR_00,E_PA_PGR_12:E_PA_PGR_17,E_SUIVI_00:E_TV_11,L_S_13").EntireRow.Hidden = True
         Case "PROJET D'ASSAINISSEMENT (PA) + SUIVI"
            Range("E_RES_00:E_PA_PGR_00,E_SUIVI_00:E_TV_11,L_S_13").EntireRow.Hidden = True
         Case "SUIVI DES TRAVAUX (SUIVI)"
            Range("E_RES_00:E_SUIVI_00,E_TDL_00:E_TV_11,L_S_13").EntireRow.Hidden = True
         Case "TRAITEMENT DE DURÉE LIMITÉE (TDL) + SUIVI"
            Range("E_RES_00:E_TDL_00,E_GF_00:E_TV_11,T_FMan_05:T_FMan_06,T_FMan_10,T_FMec_01:T_FMec_15,T_Car_FMan_10,T_Car_FMec_01:T_Car_FMec_10,T_EchE_01:T_EchE_05,T_GEO_07:T_GEO_08,M_03:M_05,L_S_13:L_E_10").EntireRow.Hidden = True
         Case "ESTIMATION DU MONTANT DE GARANTIE FINANCIÈRE (GF)"
            Range("E_RES_00:E_GF_00,E_TV_00:E_TV_11,X_07:X_10").EntireRow.Hidden = True
         Case "RAPPORT TECHNIQUE BRUXELLOIS (RT)"
            Range("E_RES_00:E_TV_00,T_FMan_05:T_FMan_06,T_FMan_10,T_FMec_08:T_FMec_12,T_FMec_15,T_Car_FMan_10,T_Car_FMec_09,T_TechS_05:T_TechS_06,T_EchE_01:T_EchE_05,T_GEO_07:T_GEO_08,D_01:M_06,L_E_01:L_E_10").EntireRow.Hidden = True
         Case "TECHNISH VERSLAG (TV)"
            Range("E_RES_00:E_TV_00,T_FMan_05:T_FMan_06,T_FMan_10,T_FMec_08:T_FMec_12,T_FMec_15,T_Car_FMan_10,T_Car_FMec_09,T_TechS_05:T_TechS_06,T_EchE_01:T_EchE_05,T_GEO_07:T_GEO_08,D_01:M_06,L_E_01:L_E_10").EntireRow.Hidden = True
      End Select
      
      
      Select Case Range("data2")
         Case "POSTES STANDARD UNIQUEMENT"
            Range("T_FMan_09,T_FMan_10,T_TechS_02,T_TechS_03,T_TechS_05:T_TechS_07,T_GEO_09,T_GEO_12").EntireRow.Hidden = True
      End Select
     
     
     Select Case Range("data3")
         Case "SOL"
            Range("T_FMan_05,T_FMan_06,T_FMan_10,T_FMec_08:T_FMec_12,T_FMec_15,T_Car_FMan_10,T_Car_FMec_09,T_EchE_01:T_EchE_05,T_GEO_07,T_GEO_08,L_E_01:L_E_10").EntireRow.Hidden = True
        Case "EAU"
            Range("T_TechS_02:T_TechS_04,L_S_01:L_S_13").EntireRow.Hidden = True
      End Select
      
           Select Case Range("data4")
         Case "MANUEL"
            Range("T_FMec_01:T_FMec_15,T_Car_FMec_01:T_Car_FMec_10").EntireRow.Hidden = True
        Case "MECANIQUE"
            Range("T_FMan_01:T_FMan_10,T_Car_FMan_01:T_Car_FMan_11").EntireRow.Hidden = True
      End Select
      
           Select Case Range("data5")
         Case "SANS TRAVAUX DE TERRAIN"
            Range("X_07:X_10").EntireRow.Hidden = True
        Case "AVEC TRAVAUX DE TERRAIN POUR ER (juste nivellement/perméa)"
            Range("T_FMan_01:T_GEO_01,T_GEO_05,T_GEO_09:T_GEO_10,T_GEO_12:T_GEO_13,D_01:X_10").EntireRow.Hidden = True
      End Select
'masque de la ligne des sélections d'étude en FR-NL selon choix de la langue
                 Select Case Range("data6")
         Case "FR"
            Range("X_02,X_04").EntireRow.Hidden = True
        Case "NL"
            Range("X_01,X_03").EntireRow.Hidden = True
      End Select
      
   End If
   




'menu dropdown list bilingues. Les cellules avec dropdown ont reçues des noms (dataX). Attention cohérence noms dans les listes (ray etc.) et les 'Case'
Dim ray As Variant, n As Long, ac As Long, Txt As String, nStr As String
Application.EnableEvents = False
ReDim ray(1 To 4, 1 To 2)
ReDim nray(1 To 8, 1 To 2)
If Target.Address = Range("data6").Address Then
       ray(1, 1) = "Commande des études de sol antérieures (étude)"
       ray(1, 2) = "Bestelling eerdere bodemonderzoeken (studie)"
       ray(2, 1) = "Bestelling eerdere bodemonderzoeken (studie)"
       ray(2, 2) = "Commande des études de sol antérieures (étude)"
       ray(3, 1) = "Commande des études de sol antérieures (dossier)"
       ray(3, 2) = "Bestelling eerdere bodemonderzoeken (dossier)"
       ray(4, 1) = "Bestelling eerdere bodemonderzoeken (dossier)"
       ray(4, 2) = "Commande des études de sol antérieures (dossier)"
        
       Txt = Range("data7").Value
        For n = 1 To UBound(ray, 1)
            If ray(n, 1) = Txt Then
                Txt = ray(n, 2)
                Exit For
            End If
        Next n
       Select Case Target.Value
            Case "FR": nStr = "Sélectionner étude de sol antérieure (étude vs. dossier),Commande des études de sol antérieures (étude),Commande des études de sol antérieures (dossier)"
            Case "NL": nStr = "Sélectionner étude de sol antérieure (étude vs. dossier),Bestelling eerdere bodemonderzoeken (dossier),Bestelling eerdere bodemonderzoeken (studie)"
        End Select
 
    With Range("data7").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=nStr
End With
 
Range("data7").Value = Txt


    nray(1, 1) = "Mobilisation carotteuse (< ou = 6 carottages)"
    nray(1, 2) = "Mobilisatie kernboormachine (< of = 6 boringen)"
    nray(2, 1) = "Mobilisatie kernboormachine (< of = 6 boringen)"
    nray(2, 2) = "Mobilisation carotteuse (< ou = 6 carottages)"
    nray(3, 1) = "Mobilisation carotteuse (> 6 carottages)"
    nray(3, 2) = "Mobilisatie kernboormachine (> 6 boringen)"
    nray(4, 1) = "Mobilisatie kernboormachine (> 6 boringen)"
    nray(4, 2) = "Mobilisation carotteuse (> 6 carottages)"
    nray(5, 1) = "Mobilisation carotteuse (> 15 carottages)"
    nray(5, 2) = "Mobilisatie kernboormachine (> 15 boringen)"
    nray(6, 1) = "Mobilisatie kernboormachine (> 15 boringen)"
    nray(6, 2) = "Mobilisation carotteuse (> 15 carottages)"
    nray(7, 1) = "Mobilisation carotteuse (> 20 carottages)"
    nray(7, 2) = "Mobilisatie kernboormachine (> 20 boringen)"
    nray(8, 1) = "Mobilisatie kernboormachine (> 20 boringen)"
nray(8, 2) = "Mobilisation carotteuse (> 20 carottages)"




Txt = Range("data8").Value


        For n = 1 To UBound(nray, 1)
            If nray(n, 1) = Txt Then
                Txt = nray(n, 2)
                Exit For
            End If
        Next n
        Select Case Target
            Case "FR": nStr = "Sélectionner carottage,Mobilisation carotteuse (< ou = 6 carottages),Mobilisation carotteuse (> 6 carottages),Mobilisation carotteuse (> 15 carottages),Mobilisation carotteuse (> 20 carottages)"
            Case "NL": nStr = "Sélectionner carottage,Mobilisatie kernboormachine (< of = 6 boringen),Mobilisatie kernboormachine (> 6 boringen),Mobilisatie kernboormachine (> 15 boringen),Mobilisatie kernboormachine (> 20 boringen)"
        End Select


With Range("data8").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nStr
End With


Range("data8").Value = Txt


End If
Application.EnableEvents = True


   
End Sub

(the main function is to hide some entire row in relation with a selected value above and also to manage traduction in some dropdown lists)
I copied your code just following this existing code.

Do you have a idea what's wrong ?


Another thing :
I made a mistake : LIST1 and LIST2 are in the same sheet.
LIST1 : sheet1, from D18 to D242
LIST2 : sheet1 from D264 to D476
But must still be separated (accessible from Combobox1/2/3/4/5 for LIST1 OR Combobox6/7/8/9/10 for LIST2).
Is it possible to adapt the code ? (not so difficult I guess).

About the blank cells/items, I don't quite understand it, when the combobox is blank & you click the dropdown button then the list will appear including the blank items, but after you type a keyword do you still want the blank items in the list?
Could you explain in more detail using an example?

The behavior of the last version is perfect.
For information, the list looks like that (by simplifying) :

---- General analysis ----

X9865 HM, BTEXN, VOCL, HAP, HM vol, monochlorobenzène
X9785 HM, Fenolindex, VOCL, HAP, HM vol, dichloroethane
X8945 HM vol, BTEXN, VOCL, chlorobenzene, 1,2 trichloro
...

---- Specific analysis ----

X0489 HM, BTEXN, ammonium, HAP, métaux lourds
M6439 HM, Fenolindex, VOCL, HAP, HM vol
V08756 HM vol, BTEXN, conductivity, chlorobenzene
...

With the last version, the user is well able to :
- see clearly the sections (the title with "----" and find more easely analysis if he chooses to select manually (if he knows more or less where to find it : it always interressant to see which analysis are under wich section)
- search by typing the code, or the names of the parameters. In this case, blanks can be effectively removed.
So that suit perfectly the goal here.

It's seems we are very close to the finnish (it's just a error of implementing code).

 

[Akuini]

But when I copy the code to my personnal file (my working file), there is a bug.
It's says 'Erreur de compilation : variable non définie' (French) and indicates the "vList1".

I'll need your sample workbook to find the source of the problem.
Could you attach your workbook (without sensitive data) ?

Another thing :
I made a mistake : LIST1 and LIST2 are in the same sheet.
LIST1 : sheet1, from D18 to D242
LIST2 : sheet1 from D264 to D476

Replace Sub allList with this:

Sub allList(a As Long)


    If a = 1 Then

            '>>> LIST 1 location

                vList1 = Sheets("Sheet1").Range("D18:D242")



    ElseIf a = 2 Then

           '>>> LIST 2 location

                vList1 = Sheets("Sheet1").Range("D264:D476")

    End If


End Sub

 

[Akuini]

Could you attach your workbook (without sensitive data) ?

Sorry, I meant:
Could you upload your workbook (without sensitive data) to a free site (such as dropbox.com or google drive)? Then put the link here.

 

[jondu]

Hello Akuini,
Here is the workbook :

Dropbox - File Deleted - Simplify your life

www.dropbox.com

We should have done that earlier

The 10 comboboxes must replace :
The cells Y261, Y262, Y263, Y264, Y265 in sheet 'Metré BXL' => refers to list1 = located in sheet Matrice BXL D18:D142
The cells Y274, Y275, Y276, Y277, Y278 in sheet 'Metré BXL' => refers to list2 = located in sheet Matrice BX D164:D276
Thoses lists are destined to be lengthened.

Another thing :
The list in sheet 'Metré BXL' is bilingual. Depending the chosen value in Y7.
FR (= French)
NL (= Nederlands)
Some menu list are made to be managed in 2 languages :
This is the case for Y30, Y44, Y59, Y75, Y102, Y140, Y154, Y198, Y199 (in yellow or green).
The cells in green have the good behavior (Y30 and Y198) : when the user switchs the language in cell Y7, the selected value in the bilingual menu cells adapt to the language.
But this function do not seem to work for the cells in yellow. if you can put me on the track to fix this, it would be appreciated.

Thank you one again ! (it's better than a professionnal service

 

[Akuini]

I read your workbook.
The problem in using combobox in your data layout is where to place it.
I think there are 2 options:
1. Place the ten combobox in col AK (i.e AK261:AK265 & AK274:AK278).
2. Use only 1 combobox in userform, the userform will be opened by clicking a button in the top of the sheet (say in col AA row 2 and 3).
It works like this:
if you want to fill in data in a cell, e.g AK261, then you need to select cell AK261 then hit the command button, the userform will appear, you do the search in the combobox, click "OK", the userform will close, the data will be entered into the cell.

Which scenario do you like? or you have another idea?

Thoses lists are destined to be lengthened.

Another thing :
The list in sheet 'Metré BXL' is bilingual. Depending the chosen value in Y7.

We will handle this problem later.