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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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.
EmployeeStart timeFinish timeHours worked
Joe Blogs
Jane Doe

<tbody>
</tbody>





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.
Employee List
Joe Blogs
Jane Doe
Andrew Peters
Sarah Cook
Joseph Belkin

<tbody>
</tbody>










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

Well-known Member
Joined
Feb 1, 2016
Messages
2,739
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

zipidytoo

New Member
Joined
Jul 13, 2014
Messages
1
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,739
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 14, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,739
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

kobebryant

New Member
Joined
May 14, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

 

kobebryant

New Member
Joined
May 14, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 29, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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?
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
56
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,075
Messages
5,570,051
Members
412,309
Latest member
essobsan
Top