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]

OK, try this:

1. The combobox is using "ON-OFF" setting.
2. The combobox is used on col D, start at D2. You can change it in this line:
Private Const xCell As String = "D2:D"

3. It uses column C to define the last row. You can change it in this line:
n = Range("C" & Rows.Count).End(xlUp).Row 'use column C to define last row
it's located in 2 sub, i.e:

• Private Sub Worksheet_SelectionChange(ByVal Target As Range)
• Sub toShowCombobox()

if you want to use a number in cell A3 to define last row then change that line to:
n = Range("A3").Value

Example:
Example

 

[emul]

Super Akuini, that's what i meant.

 

[Akuini]

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

 

[emul]

Hi Akuini,
I tested it. It works great. Now i want to use multiple searchable dropdown lis in 1 sheet. Can i copy and past de same code and adjust the variable, to the those intended for the new dropdown list. I want to use the extra searchable lists to look up the outstanding debtors and - creditors.
Ik think i can adjust the neccessary const myself, but i don't want to use 3 different on/off buttons.
For the extra lists can i simply leave out the code : Sub toOnOff() and Private Sub CommandButton1_Click , or therefore do i need more adjustments?

 

[Akuini]

I want to use the extra searchable lists to look up the outstanding debtors and - creditors.

So you need the combobox in 2 other column? what column?
Do the combobox behave like the first one i.e splitting data into 2 column? or 1 combobox for inserting data in 1 column?

For the extra lists can i simply leave out the code : Sub toOnOff() and Private Sub CommandButton1_Click , or therefore do i need more adjustments?

You don't need to add another button.

 

[emul]

1: Yes i need 2 additional comboxes; combobox2 and combobox3 and yes they behave identically, both splitting, but in stead of splitting to 2 columns they have to split to 3 columns each (1 debtor name, 2 invoice no and 3 outstanding amount) and (1 creditor name, 2 invoice no and 3 outstanding amount).
So i first make a list with the required data of the debtors and a list with the required data of the creditors, both list are on in different sheets, say sheet "List2" and sheet "List3"

Searchable dropdown list Debtors i put in column G, invoice no column H and outstanding amount in column I.
Searcheable dropdown list Creditors i put in column L, invoice no column M and outstanding amount in colum N

2: No i don't want additional on/off buttons, so if it is not necessary then it is fine

 

[Akuini]

they have to split to 3 columns each (1 debtor name, 2 invoice no and 3 outstanding amount) and (1 creditor name, 2 invoice no and 3 outstanding amount).
So i first make a list with the required data of the debtors and a list with the required data of the creditors, both list are on in different sheets, say sheet "List2" and sheet "List3"

Can you give some example of the list? maybe about 5 various example.
I need to know the pattern to split the sata into 3 part (1 debtor name, 2 invoice no and 3 outstanding amount) and (1 creditor name, 2 invoice no and 3 outstanding amount).
In the first combobox, the pattern is to split the data by the first space. For example: "a003 Boston, Massachusetts" will be split to
"a003" & "Boston, Massachusetts".

 

[emul]

well there is a sheet to book te sellings with 8 columns; 1 date, 2 debtor no, 3 debtor name, 4 invoice no 5 amount 6 paid amount 7 outstanding amount and 8 a combined column which contains debtor name, invoice no, which will be used for the searchable dropdown,( combibox2; listsheet 2)

Then for the buyings exactly the same for the creditors ( combibox3; listsheet3)

The sheet in which i want the 3 searchable lists, i want to use as a bankbook, so
there are the columns we already discussed and additional a column for the debtor nams in which the searchable dropdown list is, and after pressing enter, the splitting must take place, the debtor name must be filled in first column, the invoice no, in the second column and the outstanding amount in the third column, just a a reference.
For the creditors the same handling.

so what happens, there is a column with a date, and a amount, positive or negative, receive or paying, after that i can use the old first dropdownlist combobox1 to book the amount on accounts other than for debtors and creditors. If a debtor has paid a invoice, then i want to look up which debtors there are and what is the outstanding amount and invoice no., looked up with combobox2 and all split as reference in 3 cells.
if the amount is used to pay an supplier then i want to look up with combobox 3 which creditors there are and what is the outstanding amount and invoice no, just like the debitors.
the accountnummer for debtors and creditis are fixed, so it is not necessary to look up the account no for debtors and creditors.

 

[Akuini]

Well, I still need some examples to define the criteria to split the data correctly.

 

[emul]

I understand. I have thought and think I have a possible solution.

You may decide to determine where to split from the left or from the right.

The outstanding amount can be determined by searching for the first space from the right. The characters after the last space should be the amount and therefore split to the third column.

The invoice number is more or less easy to determine, if I ensure that it always consists of a continuous group of characters, so without a space in between, because then the invoice number can be determined by the number of characters for the last space from to the right and after the 1-last space. The invoice no should be split to the second column

The debtor name is the most difficult to determine because it can consist of several groups of characters. By first determining the amount and the invoice number, the debtor name consists of all characters, including spaces, before the invoice number. The debtor name is the active.cell in the first column.

Or i could devide the 3 variables bij a comma ,. Then you can search from Left. because the amount also contains a comma


Example:
1 Name debtor 2 Invoice number 3 Amount
customer A t. Z 1235 _ 5-7 12345,00

Column G Column H Kolom I
Column used voor List Combobox 2 splitting Custumoer A 1235_5-7 12345,00
Customer A t. Z 1235_5-7 12345,00

or
Customer A t. Z, 1235_5-7, 12345,00 splitting Customer 1235_5-7 12345,00 (without the commas after the customer name en invoice-no)

I think the separation through the comma is best.

Of course the splitting for de creditors is identically.