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.

 

[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...…………..Column I
Column used voor List Combobox 2 splitting….Custumoer A t. Z...…1235_5-7...……………..12345,00
Customer A t. Z 1235_5-7 12345,00

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

I think the separation through the comma is best

The dots are just for aesthetic presentation reasons

Of course the splitting for de creditors is identically.

 

[emul]

We could also use the ; to devide the 3 things

 

[emul]

Hi Akuini, do you want more information?

 

[Akuini]

Sorry for the late reply, it's actually harder than I thought.
I'll try it again tomorrow.

 

[emul]

No problem Akuini, i understand, i don't want to rush you.
I thank you for you reply and I will wait patiently.

 

[Akuini]

Ok, try this:
The items is separated by "; "
You can adjust the code in this part:

'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "deList"]
Private Const sList As String = "deList"

'row where the list start [in the sample: row 2 in sheet "deList" ]
Private Const rCell As Long = 2

'range where you want to use the combobox [in the sample: "B2:B20" in "sheet1", the blue area)
Private Const xCell_1 As String = "D2:D"
Private Const xCell_2 As String = "G2:G"
Private Const xCell_3 As String = "K2:K"

and in this part:

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

The workbook:
deCombobox-dinamic,visible,searchable,unique,sort,arraylist,on-off-02 - emul 2.xlsm

 

[emul]

Thank you Akuini, Wow, that is super, but i just have a small problem.
As i mentioned the lists are located on differtent sheets. One sheet which contains among other things the list Debtors, One sheets which contains among other things the Creditors and one sheet which contains the account scheme and all sheets have a differtent number of rows.
Your example is based on 1 sheet which contains all lists.

Is it possible to adjust code for the diffentent list, eg
Private Const sList1 As String = "deList"
Private Const sList2 As String = "deList2"
Private Const sList3 As String = "deList3"
I do understand it is difficult because of the extra dimension which affects the source specification
arz1 = Split("A,B,C,D", ",") ' columns where the list1 as the source of the combobox is located
arz2 = Split("A,B,C,D", ",") ' columns where the list2 as the source of the combobox is located
arz3 = Split("A,B,C,D", ",") ' columns where the list3 as the source of the combobox is located
and other parts of the code.

Although it maybe slows down the workbook a little bit, because of the copy paste, perhaps it is easier for you to still use 1 sheet for the lists, but write adjustable vba code to copy a list from a specific column of a certain sheet to a specific column of the list sheet.
For examble a ajustable vba code in sheet "deList" to copy column "g" of source "Accounts" to column "A" of sheet "deList", column "d" of source sheet "Debtors" to column "B" of sheet "deList" and copy column "e" of source sheet "Creditors" ot column "C" of sheet "deList"

 

[Akuini]

Could you upload your sample workbook to a free site such as dropbox.com & then put the link here? so I can understand the layout of the data better.

 

[Akuini]

Ok, I think I understand what you want. Try this:
I put the lsit in sheets: deList1,deList2 & deList3

You can adjust the code in this part:

'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "deList"]
Private sList As String

'row where the list start [in the sample: row 2 in sheet "deList" ]
Private Const rCell As Long = 2

'range where you want to use the combobox [in the sample: "B2:B20" in "sheet1", the blue area)
Private Const xCell_1 As String = "D2:D"
Private Const xCell_2 As String = "G2:G"
Private Const xCell_3 As String = "K2:K"

'offset from xCell (the blue area) where the cursor go after leaving the combobox
' 1 means 1 column to the right of xCell
Private Const ofs As Long = 1

and in this part:

If Not Intersect(Union(Range(xCell_1 & n), Range(xCell_2 & n), Range(xCell_3 & n)), Target) Is Nothing And Target.Count = 1 And xFlag = True Then
    'ADJUST THE CODE IN THIS PART:
    'you can have different number of columns
    ary = Split("D,G,K", ",")  ' columns where the combobox is located
    arz = Split("A,B,B", ",")  ' columns where the list as the source of the combobox is located
    ars = Split("deList1,deList2,deList3", ",")  ' columns where the list as the source of the combobox is located

The workbook:
deCombobox-dinamic,visible,searchable,unique,sort,arraylist,on-off-02 - emul 3.xlsm

Note:
In this line: ars = Split("deList1,deList2,deList3", ",") ' columns where the list as the source of the combobox is located
the description should be: sheet where the list as the source of the combobox is located

 

[emul]

Yes that is exactly what i meant. Top!
Now i still have a request if you allow it. We have determined a range, based on column C. The combobox is displayed over the entire range.
it is possible to exclude individual cells to display the combox based on content of a parallel cell in another column?
For example.
Based on column C there is a range of D2 to D16
Cells d2, d3 and d4 contain values selected via the combobox
I want to exclude D3 of showing the combobox when I press D3, if there is a certain value in column B3, eg B3 contains the value "closed".
But because B2 and B4 does not contain the value "closed", the combobox should be showed when pressing D2 or D4.
Is that possible?