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]

Hi, bobby786

How about this:
In the combobox, press ENTER to insert an entry (to the cell) then you can use CTRL+Arrow Keys to go to adjacent cell.

Dropbox

www.dropbox.com

Let me know if you need help to implement this on your workbook.

 

[bobby786]

Hi, bobby786

How about this:
In the combobox, press ENTER to insert an entry (to the cell) then you can use CTRL+Arrow Keys to go to adjacent cell.

Dropbox

www.dropbox.com

Let me know if you need help to implement this on your workbook.

You are superman !
can you help me implement this on my workbook please ? Is it possible without "CTRL+ " just curious for my knowledge purpose only. Btw Pressing Just enter does not shift the cell focus to below , ones need to use CTRL+Arrow keys to get out from that combobox?

 

[Akuini]

can you help me implement this on my workbook please ?
You set the combobox on column C,E,G and the list source are in column F,C,B in sheet LISTS.
But I can see that the combobox in col G has 2 item only in the list i.e "Pending" & "Done". So I think it'd be better if you use a simple data validation in that columns. What do you think?

Is it possible without "CTRL+ " just curious for my knowledge purpose only.
Yes, but in the combobox the down & up arrow is used for selecting the item in the list, so that's not a good idea.

Btw Pressing Just enter does not shift the cell focus to below , ones need to use CTRL+Arrow keys to get out from that combobox?
Yes,
Do you want the focus to shift to the cell below after pressing ENTER?

 

[bobby786]

@Akuini
can you help me implement this on my workbook please ?
You set the combobox on column C,E,G and the list source are in column F,C,B in sheet LISTS.
But I can see that the combobox in col G has 2 item only in the list i.e "Pending" & "Done". So I think it'd be better if you use a simple data validation in that columns. What do you think?
- Yes sure i am okay with data validation.

Is it possible without "CTRL+ " just curious for my knowledge purpose only.
Yes, but in the combobox the down & up arrow is used for selecting the item in the list, so that's not a good idea.
- how about assign LEFT and RIGHT keys for move the cursor and UP and DOWN for move the items within ComboBox?

Btw Pressing Just enter does not shift the cell focus to below , ones need to use CTRL+Arrow keys to get out from that combobox?
Yes,
Do you want the focus to shift to the cell below after pressing ENTER?
- Yes please if you can.

 

[bobby786]

@Akuini

Link of my sample worksheet updated.

BOOK_2020.V3_2020-10-03.xlsm

drive.google.com

I need more help from you in solve the below :

Col S to Z of sheet CENTRAL "Material Used" , once i have a product and its date data filled from COL C to Q , then i need to assign which extra material this product will consume , So i will assign the material which has list in LIST sheet in COL G i have setup combBox here using the same technique i learnt as your source sheet.

My Goal :
-As soon i assign a material to related product ,it should auto populate in Materials Sheet which i created.
- The related product name and invoice number in sheet CENTRAL should be populated in Materials sheet , which will show that this material will be used in that product of corresponding number of invoice. ( I have made one entry manually just to make it more easy to understand that how the data will be populated , kindly check that)
- Once the material is populated all i have to do is assign the Producer name of that material and their respective dates.
- In sheet CENTAL COL S to Z contain Material Name and Status , I want it should auto update the status based on what Material Sheet Status.


I am not sure is it possible in excel world or not. Only you can answer it.

Regards
Bobby

 

[Akuini]

It isn't clear to me, in which columns you want to have the combobox? C,E,S? or C,S?

 

[bobby786]

@Akuini
Currently i want combobox in Col C , but your code is written so beautifully that i can assign any col as source and destination. By editing part of your code as you can see it below.

'range where you want to use the combobox
'"Originally"Private Const xCell As String = "C3:C20000,E3:E20000,F3:F20000,G3:G20000"
Private Const xCell As String = "C3:C20000,S3:S20000"

'ADJUST THE CODE IN THIS PART:
'you can have different number of columns
ary = Split("C,E,F,G,S", ",") ' columns where the combobox is located
arz = Split("F,C,A,B,G", ",") ' columns where the list as the source of the combobox is located

I have no knowledge of VBA , but i am trying to learn it by means of search and try to implement it on your supplied code. My main goal i tried to explain it in Post # 104 & 105 , please let me know if you need more explanation.

Regards
Bobby

 

[Preston_Cleric]

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.

Hello Akuini, the solution that you have provided really helps my purpose, but I'm not able to access your link. Seems like it has been removed. Can you please help me here? Thank you!

 

[Akuini]

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

deCombobox-visible,searchable,unique,sort,arraylist,on-off-04

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

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.

 

[Preston_Cleric]

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

deCombobox-visible,searchable,unique,sort,arraylist,on-off-04

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

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.

Thank you very much for your prompt reply! I greatly appreciate it! I was able to access the file!