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.

 

[sstefanos]

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.

the photo i attached is from your example on post 117. i only delete the Sub ComboBox1_LostFocus().
if you do not delete the lostfocus sub the combo will open every second mouse click

 

[sstefanos]

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

It is difficult for me to replace the whole code . My workbook has one column with comboboxes and some other functions that works fine at the moment

 

[Akuini]

if you do not delete the lostfocus sub the combo will open every second mouse click

Ok, remove Sub ComboBox1_LostFocus() and add this blue line:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim arx, ary
Dim i As Long

If ComboBox1.Visible = True Then ComboBox1.Visible = False

' if selection is in a certain range (xCell) then Call toShowCombobox
If Not Intersect(Range(xCell), Target) Is Nothing And Target.CountLarge = 1 And xFlag = True Then
    arx = Split(mCell, ",")
    ary = Split(cCell, ",")
    
    For i = 0 To UBound(ary)
        If Target.Column = ary(i) Then
'            Set rCell = Sheets(sList).Cells(nRow, arx(i))
            scol = arx(i)
            Exit For
        
        End If
    
    Next
    
    Call toShowCombobox
      Else
    If ComboBox1.Visible = True Then ComboBox1.Visible = False
End If

End Sub

 

[sstefanos]

now works fine
thanks again

 

[Akuini]

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

 

[Akuini]

If anyone needs it, I posted the code of "Macro to create searchable data validation+combobox" in "Excel Articles" section. There are 3 versions of how to activate the combobox:
1. VERSION A - ACTIVATED BY DOUBLE-CLICK
2. VERSION B - ACTIVATED AUTOMATICALLY
3. VERSION C - ACTIVATED BY PRESSING ALT+RIGHT
if you want, you can reply in the Discussion tab on the thread:

Macro to create searchable data validation+combobox

Akuini submitted a new Excel article: Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the...

www.mrexcel.com

 

[Akuini]

I decided to create an add-in for this searchable combobox, called "Search deList", and I'm sharing it as freeware.
Its function is to speed up the search in the data validation list. In any cell that has data validation (with type List) pressing ALT+RIGHT will open a User Form with a combo box.You can type multiple keywords (separated by a space) in the combobox to search items on the list.
By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx.
I posted the add-in in this thread link

 

[Ankou]

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.

Hey , is

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.

Hey , is there any chance u could reupload the file ?

 

[Akuini]

Hey , is there any chance u could reupload the file ?

I wrote a newer version of the searchable combobox. Check the link in post #126.
I also created an add-in version. Check the link in post #127.

 

[Akuini]

In case anyone needs it, I just finish updating the Search deList add-in. The new version are:
Search_deList_v2.0
Search_deList_v365.1

This new version has some additional features, some of them:

• Several ways to search, like using AND or OR or LIKE operator , with or without keyword order.
• Sort the list by original order or ascending order.
• Widen or shorten the combobox width at run time.
• Insert multiple entries into the cell.

You can find it here (in post #68):

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com