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]

Hello,
Thank you again.
The best option here is option 1. It would be perfect.

 

[jondu]

Hey Akuini,
If it's to much work for you, I can finnish it myself if you put me on the track !
I am embarrassed that you yourself have to implement the code in my workbook...

 

[jondu]

And I'am also embarrassed to say this but have actually to finnish this as soon as possible...

 

[Akuini]

but have actually to finnish this as soon as possible

Well, I can't promise that we can finish it before your deadline, but try this:
See if it works as expected, then we can continue to deal with the dynamic list length & bilingual items problem.
The workbook:

METRE_BXL_Sent 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[jondu]

yeah, perfect !!
It would be very appreciate, but if it's too complicated/too much work for the bilingual items, don't worry.
And then I come to see you in Indonesia to thank you

 

[Akuini]

I forgot something:
1. I don't think you need the data validation anymore in cells AK261:AK265 & AK274:AK278.

2. Is it possible that you will delete or add some rows above row 261 in sheet Metré BXL?
If yes then we need to change the code because for example ComboBox1 is hard coded to link to cell Y261, so if say you delete 1 row above it then the combobox will still link to Y261 while it should change to Y260.

3. If you want to change the size of the combobox when you enter it then change this part to suit:
Sub setCombobox(cbo As Object) .Height = 20 .Width = 200

And if you want to change the size of the combobox when you leave it then change this part to suit:
Sub setCombobox_A(cbo As Object) .Height = 13 .Width = 170

but if it's too complicated/too much work for the bilingual items, don't worry.

Well, I'll try it, hope I can find the solution.

And then I come to see you in Indonesia to thank you

Well, please come to Indonesia.
Where are you from?

 

[jondu]

Hello,
1. Yes perfect without validation.
2. Yes insert or delete rows are possible. The hard code is indeed to be avoided.
3. Ok got it.

One detail : When I change the value in Y4, for example SOL (= List1) or EAU (= List2), there is one combobox that does not disappear :
SOL : the combobox10 is shown.
EAU : the combobox5 is shown.
The same with value in Y6 other than 'Avec travaux de terrain'.


I'm from Belgium, I'v been actually in Indonesia once (by bike, all the way long from Belgium), it's a beautiful country !

 

[Akuini]

2. Yes insert or delete rows are possible. The hard code is indeed to be avoided.

I modified the code so the combobox will link to each cell by setting the LinkedCell property.
So if you create your project in a new workbook then you can do that by running Sub toLinkCell().

One detail : When I change the value in Y4, for example SOL (= List1) or EAU (= List2), there is one combobox that does not disappear :

Actually I just realized that a combobox will not hide when the rows where it resides is hidden.
What would cause the rows in AK261:AK265 & AK274:AK278 to hide or unhide? just the change of value in Y4?

ATM try this new code, see if it works when you insert or delete some rows in sheet Metré BXL.

METRE_BXL_Sent 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[jondu]

I modified the code so the combobox will link to each cell by setting the LinkedCell property.
So if you create your project in a new workbook then you can do that by running Sub toLinkCell().

Ok perfect. And I can indeed change the link-cells if needed.

Actually I just realized that a combobox will not hide when the rows where it resides is hidden.
What would cause the rows in AK261:AK265 & AK274:AK278 to hide or unhide? just the change of value in Y4?

Possible causes of hiding rows :
1. The value in Y4 (see "SOL" or "EAU")
2. The value in Y6 (see "Sans travaux de terrain" or "avec travaux de terrain pour ER (...)"
3. The value in Y2 (see "Estimation du montant de garantie financière (GF)"
4. The hide of the row manually :
- any row or group of row above Y261, OR
- it's possible that the user hides the unwanted rows for example Y264 and Y265 + Y276 and Y277 (if he does not need to select value in there).

When the users changes a value in Y2 to Y6, he must always be able to change again this value, even after that he already have touched to the combobox and that this combobox is hidden with the last selected value in Y2 to Y6.
Example :
- selected value in Y4 is "SOL"
- the user enter a value in combobox1
- after more information, the user finally chooses "EAU" in Y4 (=> the combobox1 is hidden)
In short : the value in Y2 to Y6 must be flexible and can be changed at anytime, without interfering the behavior of the comboboxes.

 

[jondu]

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.