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 created a searchable combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type keyword in the combobox and the list will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.

Here's a screenshot:

Here's an example:
https://www.mediafire.com/file/0n1y...chable,unique,sort,arraylist,on-off.xlsm/file

Note:
1. In the top of the sheet's code module, you'll need to adjust the code in this part:
== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ====

2. In sheet2 in the example workbook you can find a slightly different setting from the one in sheet1.

 

[dougmarkham]

I created a searchable combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type keyword in the combobox and the list will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.

Hi Akuini,

Thank you very much for posting your solution---works really well and fit's the purpose perfectly!!
You're a star for contributing!

Kind regards,

Doug.

 

[Akuini]

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

 

[kalagas]

Hello and happy new year...
I tried it and when i put the mouse in cell i want to appear the combobox i get compile error and it shows me the line:
"Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer"

the popup window says:
Compile error:
User-defined type not defined

Any idea why is it happening?

 

[kalagas]

Ok i found the solution. I haven't created the ComboBox1 from developers menu in the sheet i wanted to use it. So now it works perfect

Now i have another question:
Is it possible to have multi Compoboxes in the same sheet ?

F.e i have the sheet "LISTS" which has 4 lists in columns A, B, C and D
I have a sheet named "01.01.2020" which have 6 columns A, B, C, D, E and F
I want to create 4 different comboboxes in the sheet "01.01.2020" :
1st combobox to appear in column A of the sheet "01.01.2020" and take the values of the list in the column A of the sheet "LISTS"
2nd combobox to appear in column B of the sheet "01.01.2020" and take the values of the list in the column B of the sheet "LISTS"
3rd combobox to appear in column C of the sheet "01.01.2020" and take the values of the list in the column C of the sheet "LISTS"
4th combobox to appear in column D of the sheet "01.01.2020" and take the values of the list in the column D of the sheet "LISTS".

So in fact i am asking ot to create many comboboxes in the same sheet and then for each combobox to change the following code you wrote as i need it:

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

'cell where the list start [in the sample: cell A2 in sheet "deList" ]
Private Const sCell As String = "A2"

'column where the list is (the column of sCell above)
Private Const sCol As String = "A"

'range where you want to use the combobox [in the sample: "B2:B20" in "sheet1", the blue area)
Private Const xCell As String = "A7:A42"

'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 Sub ComboBox1_GotFocus()
With ComboBox1
.MatchEntry = fmMatchEntryNone
.Value = ""
End With
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
            If IsError(Application.Match(ComboBox1.Value, Sheets(sList).Columns(sCol), 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.Count = 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.Count = 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
        .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
vList2 = Sheets(sList).Range(sCell, Sheets(sList).Cells(Rows.Count, sCol).End(xlUp)).Value

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

 

[Akuini]

Is it possible to have multi Compoboxes in the same sheet ?

I think there are 3 options:
First:
I can amend the code to implement the combobox in multiple columns, which is col A:D, but there's a problem:
after you use the combobox in a column, say col A, then the cursor will automatically move to the next column, i.e col B, which will activate the combobox again (now for entering data in col B). This behaviour may or may not suit your need.

Second:
As a workaround, we can set the combobox to appear only when you double-click a cell, by using Worksheet_BeforeDoubleClick event. It means the combobox won't appear automatically when you select a cell, you need to double-click it. But it also means you need to use mouse more often to enter data.

Third:
If you don't want to use Worksheet_BeforeDoubleClick event then we can use a keyboard shortcut to show the combobox, so you don't need mouse to do that.

So which one do you prefer?
Or do you have another idea?

 

[kalagas]

I think there are 3 options:
First:
I can amend the code to implement the combo-box in multiple columns, which is col A:D, but there's a problem:
after you use the combo-box in a column, say col A, then the cursor will automatically move to the next column, i.e col B, which will activate the combo-box again (now for entering data in col B). This behavior may or may not suit your need.

Second:
As a workaround, we can set the combo-box to appear only when you double-click a cell, by using Worksheet_BeforeDoubleClick event. It means the combo-box won't appear automatically when you select a cell, you need to double-click it. But it also means you need to use mouse more often to enter data.

Third:
If you don't want to use Worksheet_BeforeDoubleClick event then we can use a keyboard shortcut to show the combo-box, so you don't need mouse to do that.

So which one do you prefer?
Or do you have another idea?

I don't have problem with option 1.
but let me explain you why i asked for multi combo-boxes in the same sheet.
The example i wrote you is an example and not the exactly case. I have many excel files i want to use the searchable drop down list as you propose. The problem is i would like to be more flexible. In fact i may have 7 columns and i need the combo-box to appear in 3 of them and not in row (let's say i want the combo-box to appear in columns A,B and F. And the lists maybe are in "LISTS" sheets in columns C, D and E.

So if it's possible the combo-box to be flexible i would be very glad and it will save me a lot of time. That's why i asked for multi com-boxes. Maybe it's better to upload a specific excel file to understand what i mean. In this file i would like:
1) Combo-box to appear in column C of the sheet "CENTRAL" and take the values of the list in the column F of the sheet "LISTS"
2) Combo-box to appear in column E of the sheet "CENTRAL" and take the values of the list in the column C of the sheet "LISTS"
3) Combo-box to appear in column F of the sheet "CENTRAL" and take the values of the list in the column A of the sheet "LISTS"
4) Combo-box to appear in column G of the sheet "CENTRAL" and take the values of the list in the column B of the sheet "LISTS"

The file is here:
BOOK_2020.xlsm

 

[Akuini]

Ok, try this:
BOOK_2020 - 1.xlsm

Note:
- You need to adjust the code in the part that says:
YOU MAY NEED TO ADJUST THE CODE IN THIS PART:

and also in this part:

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

- I set the range where you want to use the combobox: "C3:C20000,E3:E20000,F3:F20000,G3:G20000"
row 20000 is the possible maximum row that will be use the combobox in the future, you may change that.

- Because you use multiple adjacent column, I changed this part to make the combobox appear in the activel cell not in the next cell. But you can change it back if you want to.
'.Left = Target.Offset(0, 1).Left .Left = Target.Left

EDITED:
Sorry, this part:
"C3:C20000,E3:E20000,F3:F20000,G3:G20000"
should be :
"C5:C20000,E5:E20000,F5:F20000,G5:G20000"

 

[kalagas]

Ok, try this:
BOOK_2020 - 1.xlsm

Note:
- You need to adjust the code in the part that says:
YOU MAY NEED TO ADJUST THE CODE IN THIS PART:

and also in this part:

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

- I set the range where you want to use the combobox: "C3:C20000,E3:E20000,F3:F20000,G3:G20000"
row 20000 is the possible maximum row that will be use the combobox in the future, you may change that.

- Because you use multiple adjacent column, I changed this part to make the combobox appear in the activel cell not in the next cell. But you can change it back if you want to.
'.Left = Target.Offset(0, 1).Left .Left = Target.Left

EDITED:
Sorry, this part:
"C3:C20000,E3:E20000,F3:F20000,G3:G20000"
should be :
"C5:C20000,E5:E20000,F5:F20000,G5:G20000"

It works perfect... Thanks a lot for your time. It's very flexible the way you did it.
One last thing if it's possible. If i select from list and press enter button, it goes to next cell on the right (where the cursor go after leaving the combobox).
So is it possible the cursor to go to the below cell after leaving the combobox go to the below cell and not to the next one?