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]

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

So, it's only applied to column D? not to col G & K?
Try replacing Private Sub Worksheet_SelectionChange(ByVal Target As Range) with 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) = 0 And Range(ary(0) & 1).Column = Target.Column Then
    
            For i = LBound(ary) To UBound(ary)
            If Range(ary(i) & 1).Column = Target.Column Then sList = ars(i): Exit For
            Next
    
            Call toShowCombobox
        Else
            If ComboBox1.Visible = True Then ComboBox1.Visible = False
        End If
        
Else
'    ComboBox1.Visible = False
    If ComboBox1.Visible = True Then ComboBox1.Visible = False

End If

End Sub

 

[enexa]

Hi Akuini,
first of all i want to thank you for the great contribution in this forum. Especially sharing ready code and writing clear guidance!
This is truly honorable of you.
I understand the underlying principles used. But how is it if you have comboboxes per rows and not columns?

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

'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 = "F4,F5,F60,F62"

'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


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

Private Sub ComboBox1_GotFocus()
With ComboBox1
.MatchEntry = fmMatchEntryNone
.Value = ""
End With

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

Here I want to enable comboboxes in these cells of only column F: F4,F5,F60,F62
These shall show list values of lists stored on List sheet in the following columns: E,F,I,J
e.g. combobox in F4 shall show values of column E on the List sheet.

I hope you understand my case and are able to help me out here

Good evening and cheers

 

[enexa]

this is a mistake:
ary = Split("F") should be all F4,F5,F60,F62
but again only columns work in that case, which need to be rewritten to rows somehow?

 

[Akuini]

Hi, enexa. Welcome to the Forum.

Here I want to enable comboboxes in these cells of only column F: F4,F5,F60,F62
These shall show list values of lists stored on List sheet in the following columns: E,F,I,J
e.g. combobox in F4 shall show values of column E on the List sheet.

Try this:

'=============== 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 = "List"

'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 = "F4,F5,F60,F62"

'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


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

Private Sub ComboBox1_GotFocus()
With ComboBox1
.MatchEntry = fmMatchEntryNone
.Value = ""
End With

'ADJUST THE CODE IN THIS PART:
ary = Split("F4,F5,F60,F62", ",")  ' cells where the combobox is located
arz = Split("E,F,I,J", ",")  ' columns where the list as the source of the combobox is located

End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        Select Case KeyCode
        Case 13 'Enter
           'Enter Key to fill the cell with combobox value

            Dim x As String, fm, vlist2
                
                With Sheets(sList)
'                    x = Split(ActiveCell.Address, "$")(1)
                        x = ActiveCell.Address(0, 0)

                    fm = Application.Match(x, ary, 0) - 1
                    x = arz(fm)
                    vlist2 = .Range(.Cells(rCell, x), .Cells(Rows.Count, x).End(xlUp)).Value
                End With
            
            If IsError(Application.Match(ComboBox1.Value, vlist2, 0)) Then
                
                If Len(ComboBox1.Value) = 0 Then
                    ActiveCell = ""
                    Else
                    MsgBox "Wrong input", vbCritical
                End If
            Else
                ActiveCell = ComboBox1.Value
                ActiveCell.Offset(, ofs).Activate
            End If
        
        Case 27, 9 'esc 'tab
                ComboBox1.Clear
'                ActiveCell.Offset(, ofs).Activate
                ActiveCell.Offset(, ofs).Activate
        Case Else
            'do nothing
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' if selection is in a certain range (xCell) then Call toShowCombobox
 If Not Intersect(Range(xCell), Target) Is Nothing And Target.CountLarge = 1 Then
    Call toShowCombobox
      Else
    ComboBox1.Visible = False
End If

End Sub


Sub toShowCombobox()

Dim Target As Range

Set Target = ActiveCell
' if selection is in a certain range (xCell) then show combobox
 If Not Intersect(Range(xCell), Target) Is Nothing And Target.CountLarge = 1 Then
        
 'setting up combobox property
        With ComboBox1
        .Height = Target.Height + 5
        .Width = Target.Width + 10
        .Top = Target.Top - 2
        .Left = Target.Offset(0, 1).Left
'        .Left = Target.Left
        .Visible = True
        .Value = ""
        .Activate
        
        End With
  Else
    ComboBox1.Visible = False
  End If

End Sub

Private Sub ComboBox1_LostFocus()
'    If selection is still in this sheet
    If Selection.Worksheet.Name = Me.Name Then
        
        Call toShowCombobox
        
    End If
End Sub




''========================= using "System.Collections.ArrayList" to sort list ========================
Private Sub ComboBox1_Change()

Dim dar As Object, vlist2, i As Long
Dim x As String, fm

With Sheets(sList)
'    x = Split(ActiveCell.Address, "$")(1)
    x = ActiveCell.Address(0, 0)
    fm = Application.Match(x, ary, 0) - 1
    x = arz(fm)
    vlist2 = .Range(.Cells(rCell, x), .Cells(Rows.Count, x).End(xlUp)).Value
End With

With ComboBox1
If .Value <> "" And IsError(Application.Match(.Value, vlist2, 0)) Then
    Set dar = CreateObject("System.Collections.ArrayList")
    For i = LBound(vlist2) To UBound(vlist2)

         'Use this for search patern: word*word*
'        If LCase(vList2(i, 1)) Like Replace(LCase(.Value), " ", "*") & "*" Then
        
        'Use this for search patern: *word*word*
        If LCase(vlist2(i, 1)) Like "*" & Replace(LCase(.Value), " ", "*") & "*" Then
                If Not dar.Contains(vlist2(i, 1)) And vlist2(i, 1) <> "" Then
                    dar.Add vlist2(i, 1)
                End If
        End If
    Next
        dar.Sort
       .List = dar.Toarray()
       .DropDown
End If
End With
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim vList, dar As Object, i As Long
    With ComboBox1
        If .Value = vbNullString Then
        Dim x As String, fm
            With Sheets(sList)
'                x = Split(ActiveCell.Address, "$")(1)
                    x = ActiveCell.Address(0, 0)

                fm = Application.Match(x, ary, 0) - 1
                x = arz(fm)
                vList = .Range(.Cells(rCell, x), .Cells(Rows.Count, x).End(xlUp)).Value
            End With
'        vList = Sheets(sList).Range(sCell, Sheets(sList).Cells(Rows.Count, sCol).End(xlUp)).Value
                
            
            Set dar = CreateObject("System.Collections.ArrayList")
  
            For i = LBound(vList) To UBound(vList)
                'make the list unique & has no blank
                If Not dar.Contains(vList(i, 1)) And vList(i, 1) <> "" Then
                    dar.Add vList(i, 1)
'                    dar.Add CStr(vList(i, 1))
                End If

            Next
            'sort the list
                dar.Sort
               .List = dar.Toarray()
               .DropDown

        End If
    End With
End Sub

The workbook:
deCombobox-dinamic,visible,searchable,unique,sort,arraylist - enexa.xlsm

 

[enexa]

hi Akuini,
thanks a lot for the effort! It works perfectly. Just, what I don't understand is, why are the choices once selected not saved in the designated cells?
In your example, when I chose one item, then it works, it's visible, but as soon as I jump to the next row, the item disappears.
I might not get the true purpose of this searchable dropbox, but should it not work like a dropbox in data validation? Where I can populate fields after getting them by the search window?

Thanks again for your awesome support!

 

[Akuini]

In your example, when I chose one item, then it works, it's visible, but as soon as I jump to the next row, the item disappears.

You should download the example workbook in post #15. In the workbook I explained how the combobox works.
Here's the explanation:

How it works:
- Selecting a cell in blue area (col B) will activate the combobox
- type some keywords to search, e.g "f ca"
- use up-down arrow to select an item then hit ENTER
(the selected item will be inserted into the cell)
- to leave the combobox: hit TAB or ESC
- 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
Note: selecting more than 1 cell won't activate the combobox

So, after you select an item in the combobox you need to hit ENTER.

 

[enexa]

thanks for the instructions! I didn't pay attention.
Now everything is clear and fulfill its need.
One more question for the designer in me :D
Is there a way to turn this offset off? So that I can place the cursor in the particular cell and there the combobox pops up? Or must there always be an offset of at least 1 COL.

'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

thanks in advance and wish you a nice weekend!

 

[Akuini]

Is there a way to turn this offset off?
No, because if the offset is off then the cursor will go back to the active cell then it will activate the combobox again.

So that I can place the cursor in the particular cell and there the combobox pops up?
What particular cell? and it also has the combobox?

 

[enexa]

e.g. I have 2 or more columns for several requests, how can I keep the combo box for Request 1 in its own column?
Now when I click the field, it pops up in the Request 2 column.

 

[Akuini]

how can I keep the combo box for Request 1 in its own column?

You can set the offset to go 1 cell below (not to the right). Please read post #11.