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.

 

[enexa]

thanks for the hint! However, if I change to ActiveCell.Offset(ofs).Activate it still goes to the right.
ok got it
.Left = Target.Offset(0, 0).Left

thanks so much for your help! It works fine!

How can I apply all this code to Request Column2 and so on in the most efficient way?

Wishing you a good evening!

 

[emul]

Hi Akuini,

I respond to your post #51 regarding to exclude the combobox depending the value of a cell in column B.

• Yes i meant that all comboboxes of the row, so including those of column G and K, shout be excluded.
• I changed the code of Private Sub Worksheet_SelectionChange(ByVal Target As Range), but it doesn't work properly.
  What goes wrong:
  'Cell D4 contains the value a00011, therefore Cell E4 "Clearwater, Florida'
  I changed the value of cell B4 to closed, then combobox D4 is excluded, and the combobox of D2 and D3 are still working, that's good,
  but all comboboxes of the entire range of the other columns G and K are not working anymore.
  That means vba ends alle other comboboxes, other then the comboboxes of range column D, after closing a row by changing a cell in column B

 

[enexa]

when I apply the code to include more columns, i get an error in this line:

fm = Application.Match(x, ary, 0) - 1

error: ary is 0??? Can you pls check this below and see if there is sth wrong.

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

'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
Private Const xCell As String = "F5,F6,F7,F8,F9,F21,F24,F64,F79,F80,F82,F90,F93,F95,F97,F119,F120,F144,F146,F148," _
& "G5,G6,G7,G8,G9,G21,G24,G64,G79,G80,G82,G90,G93,G95,G97,G119,G120,G144,G146,G148"


'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

'================================================================================================
Private ary
Private arz

'=================================================================================================

'ADJUST THE CODE IN THIS PART:
ary = Split("F5,F6,F7,F8,F9,F21,F24,F61,F76,F77,F79,F87,F90,F92,F94,F116,F117,F141,F143,F145", _
"G5,G6,G7,G8,G9,G21,G24,G64,G79,G80,G82,G90,G93,G95,G97,G119,G120,G144,G146,G148")  ' cells where the combobox is located
arz = Split("E,F,E,F,G,AB,AC,AE,AD,I,J,L,M,P,Q,V,AB,AM,AN,AO", _
"E,F,E,F,G,AB,AC,AE,AD,I,J,L,M,P,Q,V,AB,AM,AN,AO")  ' columns where the list as the source of the combobox is located

End Sub

Thanks in advance!

 

[enexa]

sorry, should be like this! xCell and ary have to match ofc

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

'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
Private Const xCell As String = "F5,F6,F7,F8,F9,F21,F24,F64,F79,F80,F82,F90,F93,F95,F97,F119,F120,F144,F146,F148," _
& "G5,G6,G7,G8,G9,G21,G24,G64,G79,G80,G82,G90,G93,G95,G97,G119,G120,G144,G146,G148"


'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

'================================================================================================
Private ary
Private arz

'=================================================================================================

'ADJUST THE CODE IN THIS PART:
ary = Split("F5,F6,F7,F8,F9,F21,F24,F64,F79,F80,F82,F90,F93,F95,F97,F119,F120,F144,F146,F148," _
"G5,G6,G7,G8,G9,G21,G24,G64,G79,G80,G82,G90,G93,G95,G97,G119,G120,G144,G146,G148")  ' cells where the combobox is located
arz = Split("E,F,E,F,G,AB,AC,AE,AD,I,J,L,M,P,Q,V,AB,AM,AN,AO", _
"E,F,E,F,G,AB,AC,AE,AD,I,J,L,M,P,Q,V,AB,AM,AN,AO")  ' columns where the list as the source of the combobox is located

End Sub

Thanks in advance!
[/QUOTE]

 

[Akuini]

but all comboboxes of the entire range of the other columns G and K are not working anymore.

Sorry, the code should be this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' if selection is in a certain range (xCell) then Call toShowCombobox
Dim n As Long
n = Range("C" & Rows.Count).End(xlUp).Row 'use column C to define last row
'If Not Intersect(Range(xCell & n), Target) Is Nothing And Target.Count = 1 And xFlag = True Then
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", ",")  ' sheets where the list as the source of the combobox is located
    
        If InStr(1, Cells(Target.Row, "B"), "closed", 1) Then
            If ComboBox1.Visible = True Then ComboBox1.Visible = False
        Else
            For i = LBound(ary) To UBound(ary)
            If Range(ary(i) & 1).Column = Target.Column Then sList = ars(i): Exit For
            Next
    
            Call toShowCombobox

        End If
        
Else
'    ComboBox1.Visible = False
    If ComboBox1.Visible = True Then ComboBox1.Visible = False

End If

End Sub

 

[Akuini]

sorry, should be like this! xCell and ary have to match ofc

enexa, you wrote it wrong, it should be:

ary = Split("F5,F6,F7,F8,F9,F21,F24,F61,F76,F77,F79,F87,F90,F92,F94,F116,F117,F141,F143,F145," & _
"G5,G6,G7,G8,G9,G21,G24,G64,G79,G80,G82,G90,G93,G95,G97,G119,G120,G144,G146,G148", ",")  ' cells where the combobox is located
arz = Split("E,F,E,F,G,AB,AC,AE,AD,I,J,L,M,P,Q,V,AB,AM,AN,AO," & _
"E,F,E,F,G,AB,AC,AE,AD,I,J,L,M,P,Q,V,AB,AM,AN,AO", ",") ' columns where the list as the source of the combobox is located

 

[enexa]

hi akuini,
that was it!
& _

thanks!!

 

[emul]

Hi Akuini,

I respond to your adjustment in post #65.

Now its working perfect. Thank you very much, you are a master.
I cannot upload a modified version included the adjustment of post #65 for the use of others, maybe you can?

i will try to adjust he code in my document.
Again and for now, thank you very much for you work.

 

[enexa]

I have a question regarding the combobox content. Once a value is selected, it cannot be deleted? Only changed to another value. How can I empty the combobox?

 

[Akuini]

I have a question regarding the combobox content. Once a value is selected, it cannot be deleted? Only changed to another value. How can I empty the combobox?

Please read post #56 & download the sample workbook in post #15.
- to delete 1 cell content: select the cell then hit ENTER (while combobox is empty)
- to delete more than 1 cell content: select the cells (it won't activate the combobox) then delete

Also in sheet2 in the sample workbook, you'll find an example of searchable combobox with ON-OFF option.