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]

I'm glad we finally solved this problem.
The code is a bit complex so please try it in various scenario, just in case we overlooked something.

In this case, this unique dynamic combobox (appearing and disapearing) could to the job indeed.

Actually, on second thought, I think using a single dynamic combobox will simplify the code. So here's an example:

METRE_BXL_Sent dynamic visible 1.xlsm

Shared with Dropbox

www.dropbox.com

If you're interested in this method, just let me know, I'll explain how it works & how to set it up.


Cheers.

 

[jondu]

Wouah ! Awesome ! And much better for the lisiblity
It's really better that in my dreams
So yes, I'm interrested. My workbook has evolded in the meantime (just data), so I must indeed be able to implemente the code, the combobox and commandbutton in the last workbook. So I'm indeed looking for your instructions.

For another workbook, I have posted another question. The thread is called : Auto-update selected value in a dropdown list after updating the source

Auto-update selected value in a dropdown list after updating the source

Hello, I'm looking a way to auto-update a selected value in a dropdown list after updating the source. Example : the source list : A1 : China A2 : Australia A3 : Russia In B1 : a dropdown list with the selected value Australia. In A2, I finally change Australia by South Africa => the selected...

www.mrexcel.com

In our research and your proposals, you have often approached this request. It is therefore very similar. Do not hesitate if you see a solution!
This other workbook is much more simple and contains no VBA code for the moment.

You are really good

 

[jondu]

ps. For the instructions : I am not at the office so I can not try the code in the last version. But you can just give me the less intuitive informations (points of attention if needed) and I can surely understand the rest...

 

[Akuini]

Ok, how to set it up in your actual workbook:
-On your workbook sheet "Metré BXL":
1. create an activex combobox, its name must be "Combobox1" (if it isn't then you need to amend the code to suit) .
2. create an activex commandbutton (CommandButton2 in the example workbook), you may need to amend this code to suit later:

Private Sub CommandButton2_Click() Call toOnOff End Sub

-Copy the code to sheet "Metré BXL" code module.
-Create 2 names range:
dBox1, refers to: ='Metré BXL'!$Y$261:$Y$265
dBox2, refers to: ='Metré BXL'!$Y$274:$Y$278

Note:
1. The named range address will be adjusted automatically when you add/delete some rows in sheet "Metré BXL". So it solves the adding/deleting rows problem.
2. The single dynamic hide-unhide combobox solves the hide-unhide rows problem.

To understand how it works, you can read the explanation in the example workbook "deCombobox - dinamically visible, searchable,unique,sort,arraylist,on-off.xlsm" that you've already downloaded.

Another note:
1. You may change some properties of the combobox here (note: I added .Font.Size = 12)

    'setting up combobox property
        With ComboBox1
            .Height = Target.Height + 10
            .Width = Target.Width
            .Top = Target.Top - 2
            .Left = Target.Offset(0, 1).Left
            .Font.Size = 12
            .Visible = True
            .Value = ""
            .Activate
        End With

2. In searching in the combobox, you choose comma or comma+space as the separator between the keywords. Usually we just use a space (like in Google search).
If you want to use just a space as the separator then you can replace this 2 lines:
For Each x In Split(.Value, ",") x = Trim(x)
with this 1 line:
For Each x In Split(.Value, " ")

The workbook:

METRE_BXL_Sent dynamic visible 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[jondu]

ok thank you so much, I'll do it on monday at work.

 

[jondu]

ok, everything perfect !
The only problem is that it's not possible to go backward (CTRL + Z) in the workbook, when we want to cancel an action.
Is there any solution ?

 

[Akuini]

Unfortunately, when something is changed by macro, you can't undo it.?

 

[jondu]

ok thank you. My colleagues are very happy. I explained that it is thanks to an Indonesian guy

 

[Akuini]

You're welcome, and tell your colleagues that I'm happy too ... and they owe me a beer.??