Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

[dougmarkham]

Hi Folks,

I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee".

Time-sheet worksheet.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Start time[/TD]
[TD]Finish time[/TD]
[TD]Hours worked[/TD]
[/TR]
[TR]
[TD]Joe Blogs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





I wish to be able to start typing a different employee on each row of column A (these employees are temporary workers) and have a drop-down offer me matching results to select.
Worksheet 2 is a master list of temporary employee names on.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee List[/TD]
[/TR]
[TR]
[TD]Joe Blogs[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]Andrew Peters[/TD]
[/TR]
[TR]
[TD]Sarah Cook[/TD]
[/TR]
[TR]
[TD]Joseph Belkin[/TD]
[/TR]
</tbody>[/TABLE]










Goal:
a) I envision the user to be able to start typing an employee name,
b) ...as the user is typing, I would like excel to search the master list and offer a set of matching names (a list which shrinks as the user types).
c) I would like the user to be able to select the correct employee name and have that name populate the cell (i.e., to use the down arrow key to select the desired option and press the enter key to populate the cell).

What I've already found from searches:
*There are multiple options to create an ActiveX text box for creating a searchable drop-down; however, these don't offer any way of making every cell in a column of a table to become a searchable-drop-down.
*I've found a few methods using formulas: they tend to offer only the option to click on the drop-down arrow, rather than drop-down list as you type. I'm trying to get away from options which cause the user's hands to leave the keyboard to operate a mouse.

The end goal would be to facilitate the above for existing temporary employees; however, if the employee is a new worker, I am looking to use VBA to transfer the new name from the a cell in column A to the master list via clicking a form control button.

Would anybody be willing to help me find a viable way to do this?

Due to restrictions, I don't think I'll be able to install any add-ins: just VBA, userforms and formulas.

Kind regards,

Doug.

 

[kobebryant]

@Preston_Cleric Welcome to the Forum.
Ok, here's a new file.
Check sheet1 & sheet2, there are 2 different set up of the searchable combobox. And read sheet INFO on how to set it up.

deCombobox-visible,searchable,unique,sort,arraylist,on-off-04

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Note:
If you're list is big, say more then 5000 rows than we'll need to amend the code because it could be slow.

this code is very useful, I applied this code to many things. Thank you very much

 

[tasic89]

Hi,

First, thanks for sharing this, it's working. But, I have "specific" demand.

I used "deCombobox - sheet,1 cell, searchable,filter - example 1" and I can modify it.

Specific "demand" - I would like to have 3 or more ComboBoxes with different lists on the same worksheet. E.g. - 1st combox is "names", 2nd is "surnames" and 3rd is "location" .

I don't know how to rename it all in code so 2nd list works for 2nd combobox (i guess i need to create 3 different "deList" like "deNames", "deSurnames"... ).

p.s. I did managed to make it work on single worksheet, like name for name, surname for surname... But I would like to have it all on one sheet.

 

[emul]

Hi Akuini,

Somehow i get errors using de searchable drop-down list, where in the past i didn't have a problem.
I don't know enough to solve the problem.
Please could you help me?

Underneath i send you a link to an example excel sheet

Thank you in advance.

Dropbox

www.dropbox.com

 

[Akuini]

@emul
Sorry for the late reply.
You said:
If i use the Dynamic Combobox at Row AR, related to Sheet "Grootboek" Colom M it doesn't work

In col M you have "#N/A " in some cells, just delete them all. I tried it and the combobox worked.

 

[Akuini]

@emul
I just read your PM, I can see you've already solved the problem.

 

[sstefanos]

Hello, sorry for digging an old thread but Akuini's code is fantastic.
I am using the code for my project and it works great.

the file "Copy of deCombobox-visible,searchable,unique,sort,arraylist,on-off-04" at the post 119 in the deList sheet has a cell with aa, with aaa and aaaa.
when you type a "a" in the combobox and then hit the down arrow the list is shrinking with only the words that contain "aa".
when you type a "b" in the combobox and then hit the down arrow when you meet the word "ba" the list is shrinking to the words that include the characters "ba".
I supposed that this should not be happened. Why is it happening to only particular words of the delist ?

and something more
is it possible the combobox list may contain only the words is starting from what are you typing and not the words that contains what are you typing

thank you Akuini

 

[Akuini]

when you type a "a" in the combobox and then hit the down arrow the list is shrinking with only the words that contain "aa".
when you type a "b" in the combobox and then hit the down arrow when you meet the word "ba" the list is shrinking to the words that include the characters "ba".
I supposed that this should not be happened. Why is it happening to only particular words of the delist ?

You're right, the problem arise when there is word that is part of another word, like a, aa, aaa. I've amended the code to deal with that problem.
And also amended the code so every time you choose an item in the combobox it will be automatically sent to the activecell (without needing to press Enter)
Try this:

deCombobox-visible,searchable,unique,sort,arraylist,multi column,on-off-05

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

is it possible the combobox list may contain only the words is starting from what are you typing and not the words that contains what are you typing

Try making this change:
in Private Sub ComboBox1_Change()
Change this line:
Call get_filterX
to:
Call get_filterY

and in Sub get_filterY()
Change this line:
If LCase(x) Like "*" & Replace(LCase(ComboBox1.Value), " ", "*") & "*" Then
to:
If LCase(x) Like Replace(LCase(ComboBox1.Value), " ", "*") & "*" Then

 

[sstefanos]

Thanks Akuini you are the best
It was not a problem pressing enter to sent the value to activecell but and without it it is not a problem
I put the new Private Sub ComboBox1_KeyDown, new Private Sub ComboBox1_Change() code and the new variables to my workbook . I think these are the changes i had to made for my searchable combobox coloumn and it works.
If you want to change rows-comboboxes with a mouse click you have to delete the Private Sub ComboBox1_LostFocus(). Now the only problem is if you choose something from the combo and then mouse click to another cell the combo dropdown appears to the previous cell but if you type something everything is fine. i upload a photo for the problem. First i choose something at the row 10 and then with the mouse i click at row 3. the list appears at the row 10

 

[sstefanos]

Thanks Akuini you are the best
It was not a problem pressing enter to sent the value to activecell but and without it it is not a problem
I put the new Private Sub ComboBox1_KeyDown, new Private Sub ComboBox1_Change() code and the new variables to my workbook . I think these are the changes i had to made for my searchable combobox coloumn and it works.
If you want to change rows-comboboxes with a mouse click you have to delete the Private Sub ComboBox1_LostFocus(). Now the only problem is if you choose something from the combo and then mouse click to another cell the combo dropdown appears to the previous cell but if you type something everything is fine. i upload a photo for the problem. First i choose something at the row 10 and then with the mouse i click at row 3. the list appears at the row 10

the second row is 4
First i choose something at the row 10 and then with the mouse i click at row 4. the list appears at the row 10

 

[Akuini]

If you want to change rows-comboboxes with a mouse click you have to delete the Private Sub ComboBox1_LostFocus(). Now the only problem is if you choose something from the combo and then mouse click to another cell the combo dropdown appears to the previous cell but if you type something everything is fine. i upload a photo for the problem. First i choose something at the row 10 and then with the mouse i click at row 3. the list appears at the row 10

Did you try it on your workbook or on the new example I gave you on post 117?
Because on the new example workbook I can't reproduce that behavior.

I put the new Private Sub ComboBox1_KeyDown, new Private Sub ComboBox1_Change() code and the new variables to my workbook

You should replace the whole code on your workbook with the new example code and then change the variable value as needed.