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

roykana · Jan 3, 2022

Akuini said:
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:

View attachment 52701

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

Regards,
Akuini

Dear Mr. Akuini,
I've tried your addin' i.e. "Search deList" but I've had a problem a little below my example.
CODE
0001
0002
0003
Result with Your Add-in
CODE
1
2
3
results in desired
CODE
0001
0002
0003
Thanks
roykana

Akuini · Jan 6, 2022

@roykana
Thanks for the feed back.

Try this modification:
1. Use a named ranged as the data validation formula in that particular range you're talking about and the named range must start with "CCTA", such as "CCTA1" or "CCTAX" or something else.
2. In Userform1 code module replace "Private Sub ComboBox1_Enter()" with this one:

Rich (BB 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
                    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
                
    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

Basically it does this:
if the named range start with "CCTA" then the code will load the cell text instead of value into the array.

roykana · Jan 6, 2022

Akuini said:

@roykana
Thanks for the feed back.

Try this modification:
1. Use a named ranged as the data validation formula in that particular range you're talking about and the named range must start with "CCTA", such as "CCTA1" or "CCTAX" or something else.
2. In Userform1 code module replace "Private Sub ComboBox1_Enter()" with this one:

Rich (BB 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
                    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
               
    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

Basically it does this:
if the named range start with "CCTA" then the code will load the cell text instead of value into the array.

@Akuini
I've tried your code but it's still the same result.

Akuini · Jan 6, 2022

roykana said:
I've tried your code but it's still the same result.

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

roykana · Jan 6, 2022

Akuini said:
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

@Akuini
as per your request I have uploaded a sample file in google drive
link sample file

Akuini · Jan 6, 2022

@roykana
Replace "Sub insertValue" with this one:

VBA Code:

Sub insertValue(tx 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 tx = "'" & tx
                ActiveCell = tx
            Application.EnableEvents = True
            Unload Me
    Else
            MsgBox "Wrong input", vbCritical
    End If
End Sub

Basically it does this:
if the named range start with "CCTA" then the code will add an apostrophe, so the text in the cell will begin with an apostrophe.

roykana · Jan 7, 2022

Akuini said:
@roykana
Replace "Sub insertValue" with this one:

VBA Code:

Sub insertValue(tx 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 tx = "'" & tx ActiveCell = tx Application.EnableEvents = True Unload Me Else MsgBox "Wrong input", vbCritical End If End Sub

Basically it does this:
if the named range start with "CCTA" then the code will add an apostrophe, so the text in the cell will begin with an apostrophe.

@Akuini
It went perfectly.
Thanks
roykana

Akuini · Jan 7, 2022

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

roykana · Jan 19, 2022

@Akuini
Dear Mr. Akuini,
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

Thanks
roykana

Book1

A

B

C

D

E

F

G

1

CODE

DESCRIPTION

CODE

2

0001

BAG R 0001

0001

3

0002

BAG R 0002

0002

4

0003

BAG R 0003

0003

Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G2:G4	List	=CCTA

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

Excel Facts

roykana

Active Member

Akuini

Well-known Member

roykana

Active Member

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

Excel Facts

Active Member

Well-known Member

Active Member

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