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
231
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.
 

kobebryant

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

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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

tasic89

New Member
Joined
Apr 12, 2021
Messages
7
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,839
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top