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.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[zipidytoo]

I get the following errors when I try to run deCombobox-dinamic,visible,searchable,unique,sort,arraylist,on-off-02 - emul 2.xlsm

Set dar = CreateObject("System.Collections.ArrayList") Automation error

fm = Application.Match(x, ary, 0) – 1 Type mismatch
x = D, ary = empty

I only got to the second error after I changed Set dar = CreateObject("System.Collections.ArrayList") to Set dar = CreateObject("mscorlib.ArrayList"), which is the only thing I could think of because I didn’t see System or Collections.

I am running Excel 2019 Professional Plus 64-bit. I have set a reference to mscorlib.dll

 

[Akuini]

I only got to the second error after I changed Set dar = CreateObject("System.Collections.ArrayList") to Set dar = CreateObject("mscorlib.ArrayList"), which is the only thing I could think of because I didn’t see System or Collections.

Sorry, I don't know how to solve the problem.
I suggest you start a new thread, asking why you get an error when creating System.Collections.ArrayList.


fm = Application.Match(x, ary, 0) – 1 Type mismatch
x = D, ary = empty

Not sure why ary = empty, did you use the file without changing anything?
If you've changed something then can you upload your sample workbook to dropbox?

 

[kobebryant]

I only got to the second error after I changed Set dar = CreateObject("System.Collections.ArrayList") to Set dar = CreateObject("mscorlib.ArrayList"), which is the only thing I could think of because I didn’t see System or Collections.

Sorry, I don't know how to solve the problem.
I suggest you start a new thread, asking why you get an error when creating System.Collections.ArrayList.


fm = Application.Match(x, ary, 0) – 1 Type mismatch
x = D, ary = empty

Not sure why ary = empty, did you use the file without changing anything?
If you've changed something then can you upload your sample workbook to dropbox?

Dear,
In deList1, text separated by ";"
My data, text is in 2 or 3 columns, how I can solve it, please

 

[Akuini]

Hi, kobebryant

Sorry for the late reply.

Dear,

In deList1, text separated by ";"

My data, text is in 2 or 3 columns, how I can solve it, please

Maybe this is what you want:
Example

Note:
In the combobox you can type multiple keyword separate by a space.

 

[kobebryant]

Hi, kobebryant

Sorry for the late reply.



Maybe this is what you want:
Example

Note:
In the combobox you can type multiple keyword separate by a space.

your code really really awesome. The switch on/off really good.
Please help me, I dont know how to apply your code with my data

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

 

[kobebryant]

Hi, kobebryant

Sorry for the late reply.



Maybe this is what you want:
Example

Note:
In the combobox you can type multiple keyword separate by a space.

Mr. Akuini
Now I'm stuck at this: my data source column A is Location, column B is Name
But in my sheet input column A is Name and column B is Location

 

[emul]

Hi Akuini,

The latest adjustment is the searchable combobox ..emul4 in post #89.

Regarding to that searchable dropdown list combobox i have a request.

Is it somehow possible to let a combobox search in a dynamic list choice, based on a dynamic value in a dynamic cell.

For example.
Assume there is a searchable dropdown list combo box in cell K2
There is a list1, a list2, a list3 and a list4
Cell J2 has a dynamic value (i want to choose the depending Cell myself, so it should be possible use the value in cell G2 in stead of J2)

What i want is that the searchable dropdown list combo box in cell K2 determines itself in which list the value of cell K2 must be looked up, depending on a certain value in Cell J2

e.g.
In the searchable dropdown list combobox of cell K2 i want search the value Aaliyah
if the value of cell J2 is 1000, then search Aaliyah with the combobox of cell K2 in list1
if the value of cell J2 is 2000, then search Aaliyah with the combobox of cell K2 in list2
if the value of cell J2 is 3000, then search Aaliyah with the combobox of cell K2 in list3
ect

Is it possible to built this in as a routine together with the other searchable dropdown list, so that i can choose myself in which columns the searchable dropdown list comboboxes with the dynamic list choice are?

 

[pmeghnathi]

Here's the file related to post #65
deCombobox-dinamic,visible,searchable,unique,sort,arraylist,on-off-02 - emul 4.xlsm

But I'm not sure that is what you want. Please read post #15 to understand the basic of this searchable combobox method.
Let me know if you need help.

Without box all cell

 

[bobby786]

@Akuini
You knowledge on VBA is awesome. You are super Hero .

English is not my native language , but i have to use it everyday.

I am using the following file from your post # 19
Original File link = BOOK_2020 - 2.xlsm

my sample workbook = BOOK_2020 - 2 - Copy.xlsm

I am in process of making my workbook which can be suitable for me using your shared workbook, I will need to make some changes in it , during this process i have some questions if you have free time and like to give me solution if possible i would be grateful.

Q1: When ComboBox switch if ON , if i press LEFT or RIGHT key on my keyboard the cursor should move accordingly , right now it only accepts ENTER and it will move below one cell and waiting for data input , that is OKAY but then you need to use your mouse(click) to move left or right , since i need to enter thousands of data it is more suitable if it can move LEFT , RIGHT , UP , DOWN with just keyboard keys .

Regards
Bobby