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
120
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,252
Office Version
2013
Platform
Windows
I created a searchable combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type keyword in the combobox and the list will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.

Here's a screenshot:



Here's an example:
https://www.mediafire.com/file/0n1ypr5t6kf6cg2/deCombobox_-_dinamically_visible,_searchable,unique,sort,arraylist,on-off.xlsm/file

Note:
1. In the top of the sheet's code module, you'll need to adjust the code in this part:
== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ====

2. In sheet2 in the example workbook you can find a slightly different setting from the one in sheet1.
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
120
I created a searchable combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type keyword in the combobox and the list will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.
Hi Akuini,

Thank you very much for posting your solution---works really well and fit's the purpose perfectly!!
You're a star for contributing!

Kind regards,

Doug.
 

Akuini

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

Forum statistics

Threads
1,077,985
Messages
5,337,545
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top