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

Akuini · Dec 4, 2021

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:

VBA Code:

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:

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
Open any workbook that has data validation (with list type).
In any cell that has data validation, pressing ALT+RIGHT will open a Userform with a combobox.
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 · Jan 20, 2022

@roykana

roykana said:
I use "Search_deList" from you whether you can appear information in combobox for column B even if only in column A that I use as validation

Try this:

Create a named range "=CCTA_X", refer to the range (list) in col B.
Use the named range in data validation in col G.
Replace "Sub insertValue(tx As String)" with this:

VBA Code:

Sub insertValue(tx As String)
Dim c As Range, cf As String

'insert combobox value into the active cell
    If IsNumeric(Application.Match(tx, vList, 0)) Or tx = "" Then
        Application.EnableEvents = False
            If UCase(Left(ActiveCell.Validation.Formula1, 5)) = "=CCTA" Then
                cf = ActiveCell.Validation.Formula1
                If cf = "=CCTA_X" Then
                    Set c = Evaluate(cf)
                    tx = c.Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(, -1).Text
                End If
            
                tx = "'" & tx
            End If

            ActiveCell = tx
            Application.EnableEvents = True
            Unload Me
    Else
            MsgBox "Wrong input", vbCritical
    End If
End Sub

roykana · Jan 20, 2022

Akuini said:

@roykana

Try this:

Create a named range "=CCTA_X", refer to the range (list) in col B.
Use the named range in data validation in col G.
Replace "Sub insertValue(tx As String)" with this:

VBA Code:

Sub insertValue(tx As String)
Dim c As Range, cf As String

'insert combobox value into the active cell
    If IsNumeric(Application.Match(tx, vList, 0)) Or tx = "" Then
        Application.EnableEvents = False
            If UCase(Left(ActiveCell.Validation.Formula1, 5)) = "=CCTA" Then
                cf = ActiveCell.Validation.Formula1
                If cf = "=CCTA_X" Then
                    Set c = Evaluate(cf)
                    tx = c.Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(, -1).Text
                End If
           
                tx = "'" & tx
            End If

            ActiveCell = tx
            Application.EnableEvents = True
            Unload Me
    Else
            MsgBox "Wrong input", vbCritical
    End If
End Sub

@Akuini
Dear Mr. Akuini,
Thanks to your reply, is it possible to display together in the combobox of column A and column B?

thamks
roykana

Akuini · Jan 20, 2022

roykana said:
Thanks to your reply, is it possible to display together in the combobox of column A and column B?

Can you show me an example how it should be displayed in the combobox?

roykana · Jan 20, 2022

Akuini said:
Can you show me an example how it should be displayed in the combobox?

@Akuini
Dear Mr. Akuini,
Like the screenshot below I mean.

Akuini · Jan 21, 2022

@roykana
Try this:

Create a named range "=CCTA_X", refer to the range (list) in col A (not B).
Use the named range in data validation in col G.
Replace "Private Sub ComboBox1_Enter()" & "Sub insertValue(tx As String)" with this:

VBA Code:

Private Sub ComboBox1_Enter()
Dim x, vb, ary
Dim c As Range, g As Range
Dim cf As String, msg As String
Dim i As Long, h As Long

cf = ActiveCell.Validation.Formula1

't = Timer
msg = "Can't get the range as the list source from data validation formula." & vbLf & "Please, check the formula:" _
            & vbLf & cf
        
        wFlag = False

        On Error Resume Next
        Set c = Evaluate(cf)
        On Error GoTo 0
        
        On Error GoTo skip
        
    If UCase(Left(cf, 5)) = "=CCTA" Then 'if the name range start with "CCTA"
             If Not c Is Nothing Then
             Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

                If cf = "=CCTA_X" Then
                    For Each g In c 'using range text property instead of value.
                        d(g.Text & " " & ChrW(8213) & " " & g.Offset(, 1).Text) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next

                Else
                    For Each g In c 'using range text property instead of value.
                        d(g.Text) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next
                End If
             End If
                
    Else
        If Not c Is Nothing Then
            vb = c.Value
            If Not IsArray(vb) Then
                    ReDim vb(0 To 0): vb(0) = c.Value
            End If
        Else 'if formula doesn't return a range
            If Left(cf, 1) = "=" Then GoTo skip  'if the formula doesn't return a range
            vb = (Split(cf, Application.International(xlListSeparator))) 'if the list is a string
        End If
         
         Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
            For Each x In vb
                d(CStr(x)) = Empty 'convert number to text, 1D  array, Lbound = 0
            Next
            
    End If
            
            
'            vList becomes unique, sorted & has no blank
            If d.Exists("") Then d.Remove ""
            vList = d.keys '1D array, Lbound = 0
            d.RemoveAll
            If UBound(vList) > 0 Then Call QuickSort(vList, LBound(vList), UBound(vList))
        
    With ComboBox1
        
        .MatchEntry = fmMatchEntryNone
        .Value = ""
        .List = toList(vList)

    End With

'Application.StatusBar = Timer - t
Exit Sub
skip:
On Error GoTo 0
       MsgBox msg: wFlag = True

End Sub

VBA Code:

Sub insertValue(tx As String)
Dim c As Range, cf As String

'insert combobox value into the active cell
    If IsNumeric(Application.Match(tx, vList, 0)) Or tx = "" Then
        Application.EnableEvents = False
            If UCase(Left(ActiveCell.Validation.Formula1, 5)) = "=CCTA" Then
                cf = ActiveCell.Validation.Formula1
                If cf = "=CCTA_X" Then
                    tx = Split(ComboBox1.Value, " " & ChrW(8213) & " ")(0)
                End If
            
                tx = "'" & tx
            End If

            ActiveCell = tx
            Application.EnableEvents = True
            Unload Me
    Else
            MsgBox "Wrong input", vbCritical
    End If
End Sub

Note:
I have to add a character i.e. ChrW(8213) , it looks like a dash, between the 2 entries, so it looks like this:
0001 ― BAG R 0001

roykana · Jan 21, 2022

Akuini said:

@roykana
Try this:

Create a named range "=CCTA_X", refer to the range (list) in col A (not B).
Use the named range in data validation in col G.
Replace "Private Sub ComboBox1_Enter()" & "Sub insertValue(tx As String)" with this:

VBA Code:

Private Sub ComboBox1_Enter()
Dim x, vb, ary
Dim c As Range, g As Range
Dim cf As String, msg As String
Dim i As Long, h As Long

cf = ActiveCell.Validation.Formula1

't = Timer
msg = "Can't get the range as the list source from data validation formula." & vbLf & "Please, check the formula:" _
            & vbLf & cf
       
        wFlag = False

        On Error Resume Next
        Set c = Evaluate(cf)
        On Error GoTo 0
       
        On Error GoTo skip
       
    If UCase(Left(cf, 5)) = "=CCTA" Then 'if the name range start with "CCTA"
             If Not c Is Nothing Then
             Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

                If cf = "=CCTA_X" Then
                    For Each g In c 'using range text property instead of value.
                        d(g.Text & " " & ChrW(8213) & " " & g.Offset(, 1).Text) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next

                Else
                    For Each g In c 'using range text property instead of value.
                        d(g.Text) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next
                End If
             End If
               
    Else
        If Not c Is Nothing Then
            vb = c.Value
            If Not IsArray(vb) Then
                    ReDim vb(0 To 0): vb(0) = c.Value
            End If
        Else 'if formula doesn't return a range
            If Left(cf, 1) = "=" Then GoTo skip  'if the formula doesn't return a range
            vb = (Split(cf, Application.International(xlListSeparator))) 'if the list is a string
        End If
        
         Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
            For Each x In vb
                d(CStr(x)) = Empty 'convert number to text, 1D  array, Lbound = 0
            Next
           
    End If
           
           
'            vList becomes unique, sorted & has no blank
            If d.Exists("") Then d.Remove ""
            vList = d.keys '1D array, Lbound = 0
            d.RemoveAll
            If UBound(vList) > 0 Then Call QuickSort(vList, LBound(vList), UBound(vList))
       
    With ComboBox1
       
        .MatchEntry = fmMatchEntryNone
        .Value = ""
        .List = toList(vList)

    End With

'Application.StatusBar = Timer - t
Exit Sub
skip:
On Error GoTo 0
       MsgBox msg: wFlag = True

End Sub

VBA Code:

Sub insertValue(tx As String)
Dim c As Range, cf As String

'insert combobox value into the active cell
    If IsNumeric(Application.Match(tx, vList, 0)) Or tx = "" Then
        Application.EnableEvents = False
            If UCase(Left(ActiveCell.Validation.Formula1, 5)) = "=CCTA" Then
                cf = ActiveCell.Validation.Formula1
                If cf = "=CCTA_X" Then
                    tx = Split(ComboBox1.Value, " " & ChrW(8213) & " ")(0)
                End If
           
                tx = "'" & tx
            End If

            ActiveCell = tx
            Application.EnableEvents = True
            Unload Me
    Else
            MsgBox "Wrong input", vbCritical
    End If
End Sub

Note:
I have to add a character i.e. ChrW(8213) , it looks like a dash, between the 2 entries, so it looks like this:
0001 ― BAG R 0001

@Akuini
Mr. Akuini,
I tried the code from you running perfectly but when I ran the shortcut "alt +right" became a bit long appeared combox what is possible because the original record there is 350000. One more thing if the validation data source is in filter condition then it also makes the combobox display longer
Or you have a solution so it becomes faster.
Thanks

roykana

Akuini · Jan 21, 2022

roykana said:
when I ran the shortcut "alt +right" became a bit long appeared combox what is possible because the original record there is 350000

350K records? Hmm..
Data in col A, does it have a specific format? in your example it's 3 digit, so the format is "000", what is the actual format?

roykana · Jan 21, 2022

Akuini said:
350K records? Hmm..

@Akuini
Sorry I'm late to reply
yes right

Akuini said:
Data in col A, does it have a specific format? in your example it's 3 digit, so the format is "000", what is the actual format?

The actual data type is text and the format is various in the actual data example "0","+","-","00" and others

thanks
roykana

Akuini · Jan 21, 2022

roykana said:
One more thing if the validation data source is in filter condition then it also makes the combobox display longer

While the validation data source is being filtered, do you want to load only the filtered records into the combo box?

roykana · Jan 21, 2022

Akuini said:
While the validation data source is being filtered, do you want to load only the filtered records into the combo box?

@Akuini
better clear filter in validation data source

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

Akuini

Well-known Member

Search deList v.2.1 + manual

Search deList v.365.1.1 + manual

Akuini

Well-known Member

Excel Facts

roykana

Active Member

Akuini

Well-known Member

roykana

Active Member

Attachments

Akuini

Well-known Member

roykana

Active Member

Akuini

Well-known Member

roykana

Active Member

Akuini

Well-known Member

roykana

Active Member

Similar threads

Forum statistics

Share this page

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

Well-known Member

Well-known Member

Excel Facts

Active Member

Well-known Member

Active Member

Attachments

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active 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