Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

dougmarkham · Nov 12, 2019

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.

Employee	Start time	Finish time	Hours worked
Joe Blogs
Jane Doe

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.

Employee List

Joe Blogs

Jane Doe

Andrew Peters

Sarah Cook

Joseph Belkin

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 · Feb 27, 2020

emul said:
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?

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

I will upload it if someone requests it.

ignaciob7 · Feb 29, 2020

Hello Akuini, first of all, congratulations.

Could you please post this lat modified version?

I have a spreasheet where i use the vlookup to consolidate names from different sources, so that my pivot tables have no duplicates. This solution of your is great, i would like to try to incorporate that into my excel file.

Akuini · Mar 1, 2020

ignaciob7 said:
Could you please post this lat modified version?

Here's the file related to post #65
deCombobox-dinamic,visible,searchable,unique,sort,arraylist,on-off-02 - emul 4.xlsm

But I'm not sure that is what you want. Please read post #15 to understand the basic of this searchable combobox method.
Let me know if you need help.

enexa · Mar 2, 2020

hi akuini,
I added more cells to be displayed as combo box. I can add 29 cell references and the matching columns. If I add another one, there is an error in this line:

VBA Code:

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

VBA Code:

ary = Split("F5,F6,F7,F8,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161" & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161", ",")  ' cells where the combobox is located
arz = Split("E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU" & _
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU", ",") ' columns where the list as the source of the combobox is located

i cannot find the error here.
Thanks in advance!

Akuini · Mar 2, 2020

enexa said:
I added more cells to be displayed as combo box. I can add 29 cell references and the matching columns. If I add another one, there is an error in this line:

Could you upload your sample workbook to a free site such as dropbox.com and then share the link here?
OR
Post the whole code in the module.

enexa · Mar 2, 2020

VBA Code:

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

'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,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161," & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161"

'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("F5,F6,F7,F8,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161," & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161", ",")  ' cells where the combobox is located
arz = Split(
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU" & _
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU", ",") ' columns where the list as the source of the combobox is located

Akuini · Mar 2, 2020

That's only part of the code, please post the whole code in the module, because I'll need to amend the code in various part.

enexa · Mar 2, 2020

VBA Code:

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

'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,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161," & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161"

'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("F5,F6,F7,F8,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161," & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161", ",")  ' cells where the combobox is located
arz = Split(
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU" & _
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU", ",") ' columns where the list as the source of the combobox is located
--------------------------------------------------------------------
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
        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, 0).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


Sub sdff()
Application.ScreenUpdating = True
End Sub

Akuini · Mar 2, 2020

1. This line: If Not Intersect(Range(xCell), Target) Is Nothing And Target.CountLarge = 1 Then failed because the range address is more than 255 character.
So I wrote "Sub to_xName" to create a named range for Range(xCell). You need to manually run "Sub to_xName" once for the first time and whenever you change the range address in this part:
'range where you want to use the combobox

Private Const xCell As String = "F5,F6,F7,F8,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161," & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161"

2. You missed a comma in this part ,AU" & _ it should be ,AU," & _
arz = Split("E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU," & _
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU", ",") ' columns where the list as the source of the combobox is located

Here's the amended code:

VBA Code:

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

'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,F21,F24,F40,F64,F79,F80,F82,F90,F91,F93,F95,F97,F98,F103,F104,F105,F106,F107,F112,F115,F117,F129,F132,F133,F157,F159,F161," & _
"G5,G6,G7,G8,G21,G24,G40,G64,G79,G80,G82,G90,G91,G93,G95,G97,G98,G103,G104,G105,G106,G107,G112,G115,G117,G129,G132,G133,G157,G159,G161"

'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(xCell, ",")  ' cells where the combobox is located
arz = Split("E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU," & _
"E,F,E,F,AH,AI,AH,AK,AJ,I,J,K,L,M,O,P,I,S,T,U,V,W,AI,AL,AM,AH,AB,AH,AS,AT,AU", ",") ' 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
        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("xCombo"), 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("xCombo"), 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, 0).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

Sub to_xName()
Dim c As Range
For Each x In Split(xCell, ",")
    If c Is Nothing Then
    Set c = Range(x)
    Else
    Set c = Union(c, Range(x))
    End If

Next

ThisWorkbook.Names.Add Name:="xCombo", RefersTo:=c
End Sub

enexa · Mar 3, 2020

thank you! it's working! you are my hero

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

dougmarkham

Active Member

Akuini

Well-known Member

Excel Facts

ignaciob7

New Member

Akuini

Well-known Member

enexa

New Member

Akuini

Well-known Member

enexa

New Member

Akuini

Well-known Member

enexa

New Member

Akuini

Well-known Member

enexa

New Member

Similar threads

Forum statistics

Share this page

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Active Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock