I created an Excel add-in called “Search deList”, to create searchable data validation

[Akuini]

I created an add-in called “Search deList”, and I share it as a freeware.
Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a space) in the combobox and the list will be narrowed down as you type.
I’d be appreciate if anyone can test this add-in to find any bugs or just suggesting ideas to improve its functionality.
Also, the code is not protected by password, so if anyone needs to change or add specific feature or behavior then feel free to amend the code, and if you need help for that I’ll help if I can.

How it works:

• In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox.
• Type some keywords to search, separated by a space, e.g "ma la"
• The list will be narrowed down as you type.
• The search ignores the keywords order, so the keyword "ma la" would match "Maryland" and "Alabama".
• You can use up-down arrow to select an item, then hit ENTER, the selected item will be inserted into the cell, and the userform will be closed.
• You can also use single-click to select an item, then DOUBLE-CLICK inside the box, the selected item will be inserted into the cell, and the userform will be closed.
• To leave the combobox without inserting its value to the activecell: hit TAB or ESC
• Numeric values in the list will be treated as text.
• In the Status Bar you can see how many unique items are found & displayed.
• You don't need VBA (except if you want to use additional feature as I explain below), so you can save your workbook as .xlsx.

Additional feature :
If you want, you can also activate the userform by double-clicking a cell, but you need VBA to do that. Here’s how:
Copy-paste this code into Thisworkbook code window of your workbook:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If TypeName(Selection) = "Range" Then
    Dim v
    On Error Resume Next
        v = ActiveCell.Validation.Type
    On Error GoTo 0
    'if activecell has data validation type 3
    'run "Search deList" add-in by doubke-clicking a cell
    If v = 3 Then Cancel = True: Application.Run ("toShow__dheeDAV")
End If
End Sub

Now, in every sheet, double-clicking any cell that has data validation (with List type) will open the Userform.

Image:

How to use it:

1. Install the add-in. This article explains how to install an add-in: How to install or uninstall an Excel Add-in - Excel Off The Grid
2. Open any workbook that has data validation (with list type).
3. In any cell that has data validation, pressing ALT+RIGHT will open a Userform with a combobox.
4. Play with it & see how it works.

NOTES:

• This add-in also works on dependent data validation.
• It works on large list (I tested it on 100K rows of data).
• One caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time the combobox value is inserted into the active cell.

Search_deList_v1 add-in:
Search_deList_v1

=========================================================================================

Update, 2022-Nov-18:
Search deList v.2.1 + manual.zip, it works on Excel 2007 or later:

Search deList v.2.1 + manual

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Search deList v.365.1 + manual.zip, it works on Excel 365 or later

Search deList v.365.1.1 + manual

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

This new version has some additional features, some of them:
Several ways to search, like using AND or OR or LIKE operator , with or without keyword order.
Sort the list by original order or ascending order.
Widen or shorten the combobox width at run time.
Insert multiple entries into the cell.

=======================================================================================

Regards,
Akuini

 

[Akuini]

I was wondering if you have a copy of your searchable combo box in a user form, that can be used on an ActiveX combo box control inserted on a spreadsheet?

What is the purpose of the combobox? What should it do after the search is completed?

 

[charly1]

I would like to link it to a cell, which will then activate a filter function based on the data of that linked cell

 

[Akuini]

I would like to link it to a cell, which will then activate a filter function based on the data of that linked cell

Try this;
How it works:
You can search by multiple keyword separated by a space
The list will be narrowed as you type
The search operates based on keyword order; for instance, "ma la" will match Maryland but not Alabama.
Use down/up arrow to navigate
Hit ENTER or CLICK an item to send combobox value to A3
The combobox list is unique & sorted
ListFillRange must be empty
Adjust the code in this part:
Private Const xCell As String = "A3" 'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
With Sheets("deList") vList = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Value 'adjust the range reference End With

Option Explicit
Dim nFlag As Boolean
Dim vList
Private Const xCell As String = "A3" 'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference

Sub create_List()
Dim d As Object, i As Long, x

    With Sheets("deList")
        vList = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Value 'adjust the range reference
    End With
    vList = WorksheetFunction.Sort(vList, 1, 1, False)  'sort the list
    
    Set d = CreateObject("scripting.dictionary")
    For Each x In vList
          d(x) = Empty
    Next
       If d.Exists("") Then d.Remove ""
       ComboBox1.List = d.keys
       vList = d.keys

End Sub
Private Sub ComboBox1_Click()
If nFlag = False Then
    If ComboBox1.ListIndex > -1 Then Range(xCell) = ComboBox1.Value  'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
End If
End Sub

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

Private Sub ComboBox1_DropButtonClick()
    If IsEmpty(vList) Then create_List
End Sub

Private Sub ComboBox1_Change()
Dim d As Object, i As Long, x
With ComboBox1
    If IsEmpty(vList) Then create_List
    If nFlag = False Then
        If .Value <> "" Then
            Set d = CreateObject("scripting.dictionary")
            For Each x In vList
                If LCase(x) Like "*" & Replace(LCase(.Value), " ", "*") & "*" Then
                  d(x) = Empty
                End If
            Next
               .List = d.keys
               .DropDown
        End If
    End If
End With

End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
nFlag = False
With ComboBox1
    Select Case KeyCode
        Case 13 'ENTER
             If .ListIndex > -1 Then
                Range(xCell) = .Value  'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
             Else
                MsgBox "Found nothing"
             End If
        
        Case vbKeyDown, vbKeyUp
             nFlag = True 'don't change the list when combobox1 value is changed by DOWN ARROW or UP ARROW key
    End Select
End With
End Sub

Private Sub ComboBox1_LostFocus()
    vList = Empty
    ComboBox1.Clear
End Sub

Workbook:
Akuini - searchable Combobox - sheet - nFlag - 365.xlsm

 

[charly1]

Try this;
How it works:
You can search by multiple keyword separated by a space
The list will be narrowed as you type
The search operates based on keyword order; for instance, "ma la" will match Maryland but not Alabama.
Use down/up arrow to navigate
Hit ENTER or CLICK an item to send combobox value to A3
The combobox list is unique & sorted
ListFillRange must be empty
Adjust the code in this part:
Private Const xCell As String = "A3" 'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
With Sheets("deList") vList = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Value 'adjust the range reference End With

Option Explicit
Dim nFlag As Boolean
Dim vList
Private Const xCell As String = "A3" 'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference

Sub create_List()
Dim d As Object, i As Long, x

    With Sheets("deList")
        vList = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Value 'adjust the range reference
    End With
    vList = WorksheetFunction.Sort(vList, 1, 1, False)  'sort the list
   
    Set d = CreateObject("scripting.dictionary")
    For Each x In vList
          d(x) = Empty
    Next
       If d.Exists("") Then d.Remove ""
       ComboBox1.List = d.keys
       vList = d.keys

End Sub
Private Sub ComboBox1_Click()
If nFlag = False Then
    If ComboBox1.ListIndex > -1 Then Range(xCell) = ComboBox1.Value  'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
End If
End Sub

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

Private Sub ComboBox1_DropButtonClick()
    If IsEmpty(vList) Then create_List
End Sub

Private Sub ComboBox1_Change()
Dim d As Object, i As Long, x
With ComboBox1
    If IsEmpty(vList) Then create_List
    If nFlag = False Then
        If .Value <> "" Then
            Set d = CreateObject("scripting.dictionary")
            For Each x In vList
                If LCase(x) Like "*" & Replace(LCase(.Value), " ", "*") & "*" Then
                  d(x) = Empty
                End If
            Next
               .List = d.keys
               .DropDown
        End If
    End If
End With

End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
nFlag = False
With ComboBox1
    Select Case KeyCode
        Case 13 'ENTER
             If .ListIndex > -1 Then
                Range(xCell) = .Value  'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
             Else
                MsgBox "Found nothing"
             End If
       
        Case vbKeyDown, vbKeyUp
             nFlag = True 'don't change the list when combobox1 value is changed by DOWN ARROW or UP ARROW key
    End Select
End With
End Sub

Private Sub ComboBox1_LostFocus()
    vList = Empty
    ComboBox1.Clear
End Sub

Workbook:
Akuini - searchable Combobox - sheet - nFlag - 365.xlsm

I assumed that your original combo user form would need a slight adaption for my request. It seems I was mistaken and you've outdone yourself again and crafted a lengthy code for me.

You are without doubt excel grand master of the year. Thank you so much

[An improvement to suit my needs would have been if the combo box would update the linked cell as each letter is typed into it [similar to what happens with a linked textbox], (not excluding when a selection is made with click button) - and not only when an option is actually selected. Is that an easy adaption for you to pull off in your coding or does it require proper worktime]?

 

[Akuini]

if the combo box would update the linked cell as each letter is typed into it

Let's say you type "a" but "a" isn't on the list. Do you still want to send "a" to the linked cell?

 

[Akuini]

Let's say you type "a" but "a" isn't on the list. Do you still want to send "a" to the linked cell?

Replace "Private Sub ComboBox1_Click" & "Private Sub ComboBox1_Change" with this:

Private Sub ComboBox1_Click()
If nFlag = False Then
    If ComboBox1.ListIndex > -1 Then Range(xCell) = ComboBox1.Value  'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
End If
nFlag = False
Range(xCell) = ComboBox1.Value

End Sub

Private Sub ComboBox1_Change()
Dim d As Object, i As Long, x
With ComboBox1
    If IsEmpty(vList) Then create_List
    If nFlag = False Then
        If .Value <> "" Then
            Set d = CreateObject("scripting.dictionary")
            For Each x In vList
                If LCase(x) Like "*" & Replace(LCase(.Value), " ", "*") & "*" Then
                  d(x) = Empty
                End If
            Next
               .List = d.keys
               .DropDown
        End If
    End If
    Range(xCell) = .Value
End With
End Sub

 

[charly1]

Replace "Private Sub ComboBox1_Click" & "Private Sub ComboBox1_Change" with this:

Private Sub ComboBox1_Click()
If nFlag = False Then
    If ComboBox1.ListIndex > -1 Then Range(xCell) = ComboBox1.Value  'send combobox1 value to a cell e.g. "A3" 'adjust the cell reference
End If
nFlag = False
Range(xCell) = ComboBox1.Value

End Sub

Private Sub ComboBox1_Change()
Dim d As Object, i As Long, x
With ComboBox1
    If IsEmpty(vList) Then create_List
    If nFlag = False Then
        If .Value <> "" Then
            Set d = CreateObject("scripting.dictionary")
            For Each x In vList
                If LCase(x) Like "*" & Replace(LCase(.Value), " ", "*") & "*" Then
                  d(x) = Empty
                End If
            Next
               .List = d.keys
               .DropDown
        End If
    End If
    Range(xCell) = .Value
End With
End Sub

thanks a mil

 

[Akuini]

thanks a mil

You're welcome.

 

[mark124zzz]

You're welcome.

sir , Can you help me? I want to edit the behavior of search delist. I would like it to stay in the same cell when an item in the menu has been selected. It proceeds to next cell when an option is selected. Thank You.

 

[Akuini]

sir , Can you help me? I want to edit the behavior of search delist. I would like it to stay in the same cell when an item in the menu has been selected. It proceeds to next cell when an option is selected. Thank You.

In VBAProject Search_deLits, open Userform1 code window then go to Private Sub UserForm_Initialize.
Change this part:

'where the cursor go after leaving the combobox
OffsetRow = 1: OffsetCol = 0

to this:

'where the cursor go after leaving the combobox
OffsetRow = 0: OffsetCol = 0