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

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


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:


```
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:
> ...


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


```
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:
> 
> 
> ...


@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

Book1ABCDEFG1CODEDESCRIPTIONCODE20001BAG R 0001000130002BAG R 0002000240003BAG R 00030003Sheet1Cells with Data ValidationCellAllowCriteriaG2:G4List=CCTA


----------



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

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


```
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:
> ...


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


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



```
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).
> ...


@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


----------



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

```
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 22, 2022)

@Akuini
link sample file
I share sample files but the code in excel is the actual data maybe you have a solution and the record there is 260000. Because I opened the comboxbox so slowly
thanks
roykana


----------



## Akuini (Jan 22, 2022)

@roykana 

Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)
And this is how:
  Open your VBA editor.
  Click Tools > References.
  Check the box next to “Microsoft Forms 2.0 Object Library.”



This method works only if there are no cell that has multiple lines, otherwise it won't work correctly.
This  code is faster than the earlier code but with 350K records, you still need few seconds to display the combobox.
Replace "Private Sub ComboBox1_Enter()" with this:


```
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
            
                Dim obj As New DataObject
                Dim tx As String, z As String
                Dim va, arz, k As Long
            
            If c.Parent.FilterMode = True Then
                    c.Parent.ShowAllData
            End If
             Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

                If cf = "=CCTA_X" Then
                   
                    ReDim va(1 To c.Cells.Count, 1 To 2)
                    Do
                    c.Resize(, 2).Copy
                      DoEvents
                    obj.GetFromClipboard
                    tx = obj.GetText
                    Loop Until tx <> Empty
'                      Application.Wait Now + TimeValue("0:00:01")
                   

                    tx = vbTab & Replace(tx, vbCrLf, vbTab)
                    arz = Split(tx, vbTab)
                    For i = 1 To UBound(arz) - 2 Step 2
                        k = k + 1
                        va(k, 1) = CStr(arz(i))
                        va(k, 2) = arz(i + 1)
                    Next
                   

                    For i = 1 To UBound(va, 1)
                        d(va(i, 1) & " " & ChrW(8213) & " " & va(i, 2)) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next

                Else
                    Do
                    c.Copy
                      DoEvents
                    obj.GetFromClipboard
                    tx = obj.GetText
                    Loop Until tx <> Empty

                    obj.GetFromClipboard
                    tx = obj.GetText
                   
                    For Each x In Split(tx, vbCrLf)
                        d(CStr(x)) = 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
```


----------



## roykana (Jan 22, 2022)

Akuini said:


> @roykana
> 
> Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)
> And this is how:
> ...


@Akuini 
It went perfectly 
if the position of the item column before the code column as in the picture below then I should change your code in which position?


----------



## Akuini (Jan 26, 2022)

@roykana
Sorry for the late reply.
Try this:

If the CODE is on the left then create a named range "CCTA_X1", refer to the range (list) where CODE is located.
If the CODE is on the right then create a named range "CCTA_X2", refer to the range (list) where CODE is located.
Use the named range in the data validation.
Replace "Private Sub ComboBox1_Enter()" & "Sub insertValue(tx As String)" with this:


```
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
            
                Dim obj As New DataObject
                Dim tx As String, z As String
                Dim va, arz, k As Long
            
            If c.Parent.FilterMode = True Then
                    ActiveSheet.ShowAllData
            End If
             Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

                If UCase(Left(cf, 7)) = "=CCTA_X" Then

                    ReDim va(1 To c.Cells.Count, 1 To 2)

                    If cf = "=CCTA_X1" Then
                        Set c = c.Resize(, 2)
                    ElseIf cf = "=CCTA_X2" Then
                        Set c = c.Offset(, -1).Resize(, 2)
                    End If
                   
                    Do
                    c.Copy
                      DoEvents
                    obj.GetFromClipboard
                    tx = obj.GetText
                    Loop Until tx <> Empty

                    tx = vbTab & Replace(tx, vbCrLf, vbTab)
                    arz = Split(tx, vbTab)
                   
                    If cf = "=CCTA_X1" Then
                        For i = 1 To UBound(arz) - 2 Step 2
                            k = k + 1
                            va(k, 1) = CStr(arz(i))
                            va(k, 2) = arz(i + 1)
                        Next
                    ElseIf cf = "=CCTA_X2" Then
                        For i = 1 To UBound(arz) - 2 Step 2
                            k = k + 1
                            va(k, 1) = CStr(arz(i + 1))
                            va(k, 2) = arz(i)
                        Next
                    End If
                   

                    For i = 1 To UBound(va, 1)
                        d(va(i, 1) & " " & ChrW(8213) & " " & va(i, 2)) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next

                Else
                    Do
                    c.Copy
                      DoEvents
                    obj.GetFromClipboard
                    tx = obj.GetText
                    Loop Until tx <> Empty

                    obj.GetFromClipboard
                    tx = obj.GetText
                   
                    For Each x In Split(tx, vbCrLf)
                        d(CStr(x)) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next
                End If
             End If
               
    Else
        If Not c Is Nothing Then
        c.AutoFilter
            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
```


```
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 UCase(Left(cf, 7)) = "=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
```


----------



## roykana (Jan 26, 2022)

Akuini said:


> @roykana
> Sorry for the late reply.
> Try this:
> 
> ...


@Akuini 
Thank you very much for your reply. You're the best
It went perfectly.

thanks
roykana


----------



## abdelfattah (Jan 27, 2022)

Hi Akuini,

despite  of I  try following   your  instructions  , but I  don't  know  how  deal  with  your  tool . I  checked  the  tool  is  entered  in adds in as  in  picture  and  create  datavalidation  and  press alt+right   but  nothing happens.


----------



## Akuini (Jan 27, 2022)

abdelfattah said:


> I checked the tool is entered in adds in as in picture and create datavalidation and press alt+right but nothing happens.


Not sure what happen.

Does the data validation work normal? 
Can you give me an example of the data validation formula you're using? 
Open the VBE window, see if there's "vbaproject(Search_deList)".


----------



## abdelfattah (Jan 27, 2022)

> Can you give me an example of the data validation formula you're using?


this  is  the formula in I2

```
=$C$2:$C$5
```



> Open the VBE window, see if there's "vbaproject(Search_deList)".


it  doesn't  show  at all .


----------



## Akuini (Jan 27, 2022)

> it doesn't show at all .


I'm not using Excel 2010, so I don't know how to check it. Probably it has something to do with Trusted Location.
Try putting the add-in in Trusted Location.


----------



## Kunal Ravi (Mar 8, 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.
> ...


Love you bro, thank you so much
Jesus, plz bless him


----------



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

```
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 (Mar 8, 2022)

Kunal Ravi said:


> Love you bro, thank you so much


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


----------



## enexa (Jun 24, 2022)

Akuini, thanks for your kindness to share this amazing functionality as a very easy to use add-in.
I am sure, this will benefit so many users
You are the greatest!
Take care and again big thank you!!!!


----------



## Akuini (Jun 24, 2022)

enexa said:


> Akuini, thanks for your kindness to share this amazing functionality as a very easy to use add-in.
> I am sure, this will benefit so many users
> You are the greatest!
> Take care and again big thank you!!!!


You're welcome,  thanks for the feedback.
I'm glad you found this useful.


----------



## enexa (Jun 27, 2022)

Hi Akuini,
I have a question regarding disabling the userform for a specific cell which has a drop-down list.
This is due to the reason that another macro does not work, if the user picks the value from the drop-down list through activating the user form.
The other macro is the automatic autofilter which is triggered by 
Private Sub Worksheet_Change(ByVal Target As Range)

Looking forward to your advice. 
Best regards
Enexa


----------



## Akuini (Jun 28, 2022)

enexa said:


> I have a question regarding disabling the userform for a specific cell which has a drop-down list.


For example: if data validation formula in the specific cell is "=xhas_1"
In Module "xDAV_1", in "Public Sub toShow__dheeDAV()", add the blue line:


```
Public Sub toShow__dheeDAV()

Dim vx
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
     If v = 3 Then
        
        'validation with specific formula won't activate the Userform
        If ActiveCell.Validation.Formula1 = "=xhas_1" Then Exit Sub
        
        vx = Evaluate(ActiveCell.Validation.Formula1)
        
        UserForm1.Show
        
     End If
End If

End Sub
```

Any cell in any workbook & any sheet that has data validation formula as  "=xhas_1"  won't activate the Useform. So if you want it only in a specific workbook & sheet then the formula should be unique.


----------



## enexa (Jun 29, 2022)

Hi Akuini,
thanks for the help.


Akuini said:


> For example: if data validation formula in the specific cell is "=xhas_1"


What is meant with data validation formula?
I have a dependent drop-down list there
=INDIRECT(SUBSTITUTE($H$2," ","_"))
However, I get a compile error: "Expected: Then or GoTo the formula should be unique."
Are the quotes problematic?


----------



## Akuini (Jun 29, 2022)

enexa said:


> I have a dependent drop-down list there
> =INDIRECT(SUBSTITUTE($H$2," ","_"))



Sorry, I thought you were using a named range as data validation formula. In my example "xhas_1" is a named range.
The above method won't work on your case.
Let's try this: 
Say the workbook name is "myBook.xlsm" and the sheet name is "Sheet1" and the specific cells (to be excluded from activating the userform) are A1:A10.
Replace "Public Sub toShow__dheeDAV()" with this:



```
Public Sub toShow__dheeDAV()

Dim vx

   If ActiveWorkbook.Name = "myBook.xlsm" Then
        If ActiveSheet.Name = "Sheet1" Then
            If Not Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then Exit Sub
        End If
   End If

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
     If v = 3 Then
        vx = Evaluate(ActiveCell.Validation.Formula1)
        
        UserForm1.Show
                
     End If
End If

End Sub
```


change the value in the blue lines to suit.
Don't forget to save the code module.

Note:  If you're interested, I wrote a macro to set up dependent data validation, with this method you only need 1 main table as the source, 1 helper table (created by macro), 1 named range & 1 UDF. Check post #12 in this thread:









						3 or More Dependent Data Validation, with VBA, easy to set up
					

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...




					www.mrexcel.com


----------



## Akuini (Jun 29, 2022)

enexa said:


> This is due to the reason that another macro does not work, if the user picks the value from the drop-down list through activating the user form.
> The other macro is the automatic autofilter which is triggered by
> Private Sub Worksheet_Change(ByVal Target As Range)


Ah, I forgot something, the reason you don't want to activate the Userform is because it won't trigger "Private Sub Worksheet_Change". So, forget the method I suggested earlier. In UserForm1 code module, just comment or remove 2 blue lines in "Sub insertValue"



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


----------



## enexa (Jun 30, 2022)

Hi Akuini,
thanks for the support.
Actually, this makes sense now  to disable the Application.EnableEvents property so that the other macros on a worksheet can run.
Could you explain why you enabled it in here? To prevent an infinite loop?
Thanks for this hint. As usual you are the best! 
I am also happy to check out your other macro!
Cheers,
Enexa


----------



## Akuini (Jul 1, 2022)

enexa said:


> Actually, this makes sense now  to disable the Application.EnableEvents property so that the other macros on a worksheet can run.
> Could you explain why you enabled it in here? To prevent an infinite loop?



The Search deList add-in is base on a code I've written for a searchable combobox that used an active-x combobox on sheet. The code is a bit complex and it deals with many event procedure which is confusing. So I put "Application.EnableEvents = False" to make things simpler.
But now after reading your comment, I think it's better to remove "Application.EnableEvents = False" in the add-in, because the Userform combobox in the add-in doesn't relate with many event procedure.
Thanks for bringing up this issue. 
And if you have any other feedback to improve the add-in's functionality, I'd love to hear it.


----------



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

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


----------



## enexa (Jul 1, 2022)

You are welcome  and yes, I will keep digging and let you know in case I encounter other issues.


----------



## roykana (Aug 25, 2022)

Dear mr @Akuini
as per post #24 I want the item column to appear when I have selected the code in the combobox .
is there a best solution?
thanks


```
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
            
                Dim obj As New DataObject
                Dim tx As String, z As String
                Dim va, arz, k As Long
            
            If c.Parent.FilterMode = True Then
                    ActiveSheet.ShowAllData
            End If
             Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

                If UCase(Left(cf, 7)) = "=CCTA_X" Then

                    ReDim va(1 To c.Cells.Count, 1 To 2)

                    If cf = "=CCTA_X1" Then
                        Set c = c.Resize(, 2)
                    ElseIf cf = "=CCTA_X2" Then
                        Set c = c.Offset(, -1).Resize(, 2)
                    End If
                   
                    Do
                    c.Copy
                      DoEvents
                    obj.GetFromClipboard
                    tx = obj.GetText
                    Loop Until tx <> Empty

                    tx = vbTab & Replace(tx, vbCrLf, vbTab)
                    arz = Split(tx, vbTab)
                   
                    If cf = "=CCTA_X1" Then
                        For i = 1 To UBound(arz) - 2 Step 2
                            k = k + 1
                            va(k, 1) = CStr(arz(i))
                            va(k, 2) = arz(i + 1)
                        Next
                    ElseIf cf = "=CCTA_X2" Then
                        For i = 1 To UBound(arz) - 2 Step 2
                            k = k + 1
                            va(k, 1) = CStr(arz(i + 1))
                            va(k, 2) = arz(i)
                        Next
                    End If
                   

                    For i = 1 To UBound(va, 1)
                        d(va(i, 1) & " " & ChrW(8213) & " " & va(i, 2)) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next

                Else
                    Do
                    c.Copy
                      DoEvents
                    obj.GetFromClipboard
                    tx = obj.GetText
                    Loop Until tx <> Empty

                    obj.GetFromClipboard
                    tx = obj.GetText
                   
                    For Each x In Split(tx, vbCrLf)
                        d(CStr(x)) = Empty 'convert number to text, 1D  array, Lbound = 0
                    Next
                End If
             End If
               
    Else
        If Not c Is Nothing Then
        c.AutoFilter
            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
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 UCase(Left(cf, 7)) = "=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
```


----------



## Akuini (Aug 25, 2022)

roykana said:


> I want the item column to appear when I have selected the code in the combobox .


I don't understand what you mean. Can you give me an example?


----------



## roykana (Aug 25, 2022)

Akuini said:


> I don't understand what you mean. Can you give me an example?


Dear Mr. Akuini,
below I give an example, so I use a formula to pop up in the column of the item so I want without a formula with me selecting the item in the code in the combobox then automatically the item appears in the column "ITEM"

create searchable data validation..xlsxAB1CODEITEM2*001TEST R *0013222.....TEST R 222.....40002TEST R 00025-113TEST R -113SEARCHCell FormulasRangeFormulaB2:B5B2=IF(A2="","",INDEX(MASTERVALIDATION,MATCH(TRUE,EXACT(A2,MASTERVALIDATION[CODE]),0),1))Press CTRL+SHIFT+ENTER to enter array formulas.Cells with Data ValidationCellAllowCriteriaA2:A5List=CCTA_X2



Thanks


----------



## Akuini (Aug 26, 2022)

@roykana 
1. Data-validation in col B, is that dependent data-validation?
2. You didn't show me the data source (in MASTERVALIDATION). 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 (Aug 26, 2022)

Akuini said:


> @roykana
> 1. Data-validation in col B, is that dependent data-validation?
> 2. You didn't show me the data source (in MASTERVALIDATION). 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
thanks reply from you.


Akuini said:


> @roykana
> 1. Data-validation in col B, is that dependent data-validation?


yes, it depends on the validation data


Akuini said:


> 2. You didn't show me the data source (in MASTERVALIDATION). 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.


according to your request this link that sample files are shared in the google drive link
link sample file

thanks


----------



## Akuini (Sep 3, 2022)

@roykana 
Sorry for the late reply.
Data in col ITEM, are they always "TEST R " & col CODE?
So why not just using a simple formula in col B, like:
`="TEST R "&A2`

or if that's not the case then you can use this UDF:

```
Function toItem(c As Range) As String
Dim f As Range

Set f = Sheets("MASTERVALIDATION").ListObjects("MASTERVALIDATION").ListColumns(2).DataBodyRange.Find(What:=c.Text, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not f Is Nothing Then
        toItem = f.Offset(, -1).Value
    End If
End Function
```
but you need to save the workbook as .xlsm








						Roykana - create searchable data validation #1
					

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


----------



## roykana (Sep 6, 2022)

Akuini said:


> @roykana
> Sorry for the late reply.
> Data in col ITEM, are they always "TEST R " & col CODE?
> So why not just using a simple formula in col B, like:
> `="TEST R "&A2`


@Akuini
Thank you for your reply 
 and sorry I replied late. it's just an example so it didn't originally come from the word "TEST"


Akuini said:


> or if that's not the case then you can use this UDF:
> 
> ```
> Function toItem(c As Range) As String
> ...


is there any other solution besides udf because the record there are hundreds of thousands so it doesn't make it very slow?

thanks


----------



## RajK2005 (Sep 24, 2022)

Hi,

You shared this file with me a few days ago and it's working great except when the list has more 33441. If I enter a value which is in the dropdown but is below row 33441, then it gives me "Wrong input" error. Any idea why that could happen?


----------



## craig_toohey (Sep 26, 2022)

Hello @Akuini , I just found your post and your very helpful add-in.  I'm wondering if it's possible to make the combo box wider?  The text strings in my list are fairly long and I'm unable to see the full text string.  Twice as wide would be amazing.

Unfortunately I don't have really anything in the way of coding skills so please take that into account with your response.  Hoping you will see this, thanks in advance!

Craig


----------



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

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


----------



## craig_toohey (Sep 26, 2022)

craig_toohey said:


> Hello @Akuini , I just found your post and your very helpful add-in.  I'm wondering if it's possible to make the combo box wider?  The text strings in my list are fairly long and I'm unable to see the full text string.  Twice as wide would be amazing.
> 
> Unfortunately I don't have really anything in the way of coding skills so please take that into account with your response.  Hoping you will see this, thanks in advance!
> 
> Craig


I have no idea how I got so lucky, but I opened Visual Basic and was able to intuitively figure out how to do this.  Works perfectly now.  Sorry to bother!


----------



## Akuini (Sep 28, 2022)

RajK2005 said:


> Hi,
> 
> You shared this file with me a few days ago and it's working great except when the list has more 33441. If I enter a value which is in the dropdown but is below row 33441, then it gives me "Wrong input" error. Any idea why that could happen?


Not sure why that happened, I can't investigate without seeing the data.
If you try it on different set of data that also has more than 33441 rows, does it work or not?


----------



## Akuini (Sep 28, 2022)

craig_toohey said:


> I have no idea how I got so lucky, but I opened Visual Basic and was able to intuitively figure out how to do this.  Works perfectly now.  Sorry to bother!


Glad you figured it out.  
Hope this add-in is useful for you.


----------



## Akuini (Sep 28, 2022)

roykana said:


> is there any other solution besides udf because the record there are hundreds of thousands so it doesn't make it very slow?


Sorry for the late reply.
I think this problem is similar to the one in post #24, have you tried amending the code using the method in post #24 to deal with your new problem?


----------



## Akuini (Sep 28, 2022)

RajK2005 said:


> Hi,
> 
> You shared this file with me a few days ago and it's working great except when the list has more 33441. If I enter a value which is in the dropdown but is below row 33441, then it gives me "Wrong input" error. Any idea why that could happen?


Try this:

Open vba window
Open VBAProject Search_deList_v1.xlam
Open UseForm1 code window
Replace Sub insertValue(tx As String) with this one:


```
Sub insertValue(tx As String)
'insert combobox value into the active cell
    If IsNumeric(Application.Match(tx, ComboBox1.List, 0)) Or tx = "" Then
            ActiveCell = tx
            Unload Me
    Else
            MsgBox "Wrong input", vbCritical
    End If
End Sub
```

I'll investigate what the is the source of the problem, it probably *Application.Match *has limit on how many items in an array that can processed.


----------



## craig_toohey (Sep 28, 2022)

Akuini said:


> Glad you figured it out.
> Hope this add-in is useful for you.


Super useful.  Quick question - is the sorting order determined by alphanumeric sorting?  Would it be possible to change the sorting to match the source data's sorting order?


----------



## Akuini (Sep 28, 2022)

craig_toohey said:


> Super useful.  Quick question - is the sorting order determined by alphanumeric sorting?  Would it be possible to change the sorting to match the source data's sorting order?


Yes, it's alphanumeric. If you want keep the sorting as the source data's sorting order then try this:

Open vba window
Open VBAProject Search_deList_v1.xlam
Open UseForm1 code window
In "Private Sub ComboBox1_Enter", comment or remove this line:


```
If UBound(vList) > 0 Then Call QuickSort(vList, LBound(vList), UBound(vList))
```

Note:
If you have large data, say more than 80K rows, then you may want to also make a change in the code as describes in post #55.


----------



## craig_toohey (Sep 28, 2022)

Akuini said:


> Yes, it's alphanumeric. If you want keep the sorting as the source data's sorting order then try this:
> 
> Open vba window
> Open VBAProject Search_deList_v1.xlam
> ...


Excellent, worked perfectly.  Thank you so much!

Last question - if I share my .xlam file with others, will the changes made to the code be applied in their excel versions, or will they have to do these same code changes on their respective computers?


----------



## Akuini (Sep 28, 2022)

craig_toohey said:


> Excellent, worked perfectly.  Thank you so much!
> 
> Last question - if I share my .xlam file with others, will the changes made to the code be applied in their excel versions, or will they have to do these same code changes on their respective computers?


Just share .xlam file that has been amended (not the original one), they will get the amended version automatically.


----------



## darksider_hp9x (Sep 30, 2022)

Thanks Akuini for sharing a great Add-in. I have 1 error for data with many "-" with about 4000 rows. Can you help me fix it? I'm not good vba


----------



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

```
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 (Sep 30, 2022)

@darksider_hp9x 
Welcome to the Forum.
Your code is not the code from this Search deList add-in but it's from my other thread (which uses macro instead of add-in):








						Macro to create searchable data validation+combobox
					

Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the list when the list is long. This searchable data validation+combobox is a way to deal with that problem. It has these behavior: 1. The...




					www.mrexcel.com
				




so please reply on that thread.

But if you want to use this Search deList add-in then you can check post #1 (in this thread) on how to install it.








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

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




					www.mrexcel.com
				



In my opinion, to have this searchable functionality, using this add-in is easier than using a macro .


----------



## RajK2005 (Oct 2, 2022)

Akuini said:


> Try this:
> 
> Open vba window
> Open VBAProject Search_deList_v1.xlam
> ...


It worked. Thanks a lot. This has been such a helpful tool. Saved me a lot of time.


----------



## Akuini (Oct 3, 2022)

RajK2005 said:


> It worked. Thanks a lot. This has been such a helpful tool. Saved me a lot of time.


You're welcome. And thanks for raising the issue. I need any feedback like this to improve the add-in.
I plan to release version 2.0. This new version has some additional feature, some of them:
you can choose several ways to search, like using AND or OR or LIKE operator , with or without keyword order.
you can choose to sort the list by original order or ascending order.
you can widen the combobox at run time 

I'd like to hear if you have any ideas on what new features should be added to the add-in.


----------



## roykana (Oct 5, 2022)

roykana said:


> @Akuini
> Thank you for your reply
> and sorry I replied late. it's just an example so it didn't originally come from the word "TEST"
> 
> ...





Akuini said:


> @roykana
> Sorry for the late reply.
> Data in col ITEM, are they always "TEST R " & col CODE?
> So why not just using a simple formula in col B, like:
> ...


@Akuini ,
Dear Mr. Akunini

I have a problem with the code entry of the function you created. I entered the code "*478" should be the result of the item "TEST R 478" but the result of the item is different which is "TEST R 336478". I also attach a screenshot below. Please solution.

Thanks


----------



## noorul (Nov 10, 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.
> ...


Hi Akuini

The download from mediafire is not working


----------



## Akuini (Nov 10, 2022)

noorul said:


> Hi Akuini
> 
> The download from mediafire is not working


I just tried downloading it, it works.
Please, try again, if it still doesn't work then I'll upload it to dropbox.


----------



## forothergaming (Nov 14, 2022)

Great add in mr. Akuini. But could you add more in it such as multiple pick from the list? 🙏


----------



## Akuini (Nov 16, 2022)

I just finish updating the Search deList add-in. The new version are:

*Search_deList_v2.0*, it works on Excel 2007 or later
Download Search_deList_v2.0.xlam + Manual.pdf:
Search deList v.2 + manual.zip

*Search_deList_v365.1*, it works on Excel 365 or later
Download Search_deList_v365.1.xlam + Manual.pdf:
Search deList v.365.1 + manual.zip

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

*The keys to use when the cursor is in the combobox:*

ENTER, SINGLE-CLICK
You can use up-down arrow to select an entry, then hit ENTER or SINGLE-CLICK, the selected entry will be inserted into the cell. The focus will move to the cell *below the active cell*.

ESC
To leave the combobox without inserting its value to the active cell: hit ESC or click the Exit button.

F1 & F2
Use F1 & F2 to narrow or widen the combobox size at run time. The change will be preserved throughout the session (until you close Excel).

F5
to toggle sort order: original (the default) or ascending (A-Z). The selected sorting order will be preserved throughout the session (until you close Excel).

F8
to toggle insert mode: continuous (the default) or non-continuous.
In continuous mode, inserting an entry to the cell won’t close the Userform if the cell below the active cell has data validation.

F9
insert multiple entries into the cell (separated by “, ”).

*The keys to use when the cursor is in the textbox*
KEYDOWN, KEYUP
Go to the cell below/above the active cell without exiting Userform & without inserting any values to the active cell.

*The search rules:*

There is a textbox to the left of the combobox. You can have different search mode by typing “1” or “2” or “3” on the textbox or leave it empty.

1. The textbox is empty, this is the default. Search without keyword order & use AND operator.

• Type “ma la”. It would match “Maryland” and “Alabama”, but not “Land”







2. Type “1” > search with keyword order & a space acts like “*”.

• “ ma la”, (there’s a space in the beginning). It would match “Maryland”, “In Maryland”, but not “Alabama”.

• “ma la”, (no space in the beginning). It would match “Maryland”, but not “In Maryland”. So, the entry must begin with “ma” & has “la”.





3. Type “2” > search without keyword order & use OR operator.

• “ma la”. It would match “Maryland”, “Alabama”, “Land”, “remain”.


4. Type “3” > search with keyword order & use LIKE operator.

• “##”. It would match “34”, but not “345”.

• “## a”. It would match “34 A”, but not “A 34”.

*How to use it:*
Install the add-in: Open Excel > go to Developer > Add-ins > Excel Add-ins > browse the add-in file that you have downloaded > make sure the add-in is ticked > OK.
Open any workbook that has data validation (with list type).
In any cell that has data validation (with list type that returns a range), pressing ALT+RIGHT will open the searchable combobox.

*Notes:*

I share this add-in as a freeware.
This add-in isn’t password protected.
In the Userform caption, you can see how many unique entries are found & displayed.
Numeric values in the list will be treated as text.
It works on large lists (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 history, so at that time you can't use UNDO. In this case it happens every time the combobox entry is inserted into the cell.

*Disclaimer*:
This software is provided as is without warranty of any kind.

Indonesia, November 2022

Regards,

*Akuini*


----------



## Akuini (Nov 16, 2022)

forothergaming said:


> Great add in mr. Akuini. But could you add more in it such as multiple pick from the list?


Do you mean you want to insert multiple entries to the cell?
Try using the new version in post #68.
F9
insert multiple entries into the cell (separated by “, ”).


----------



## forothergaming (Nov 16, 2022)

Akuini said:


> Do you mean you want to insert multiple entries to the cell?
> Try using the new version in post #68.
> F9
> insert multiple entries into the cell (separated by “, ”).


That's it! Works perfectly Mr. Akuini Great adds always 🙏👍


----------



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

```
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 (Nov 16, 2022)

forothergaming said:


> That's it! Works perfectly Mr. Akuini Great adds always 🙏👍


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

@RajK2005
In case you're interested, I just posted a new version of Search deList in post #68.


----------



## forothergaming (Nov 16, 2022)

Akuini said:


> Do you mean you want to insert multiple entries to the cell?
> Try using the new version in post #68.
> F9
> insert multiple entries into the cell (separated by “, ”).


Unfotunely they're not really inserted, only one was in it when I press enter. Could you fix it please?


----------



## Akuini (Nov 16, 2022)

forothergaming said:


> Unfotunely they're not really inserted, only one was in it when I press enter. Could you fix it please?


Please read the manual, you need to press F9 instead of Enter to insert multiple entries.


----------



## forothergaming (Nov 16, 2022)

Akuini said:


> Please read the manual, you need to press F9 instead of Enter to insert multiple entries.


So I have to use F9 then ESC intead of enter?


----------



## Akuini (Nov 16, 2022)

forothergaming said:


> So I have to use F9 then ESC intead of enter?


Yes


----------



## Akuini (Nov 17, 2022)

craig_toohey said:


> Hello @Akuini , I just found your post and your very helpful add-in. I'm wondering if it's possible to make the combo box wider? The text strings in my list are fairly long and I'm unable to see the full text string. Twice as wide would be amazing.


I just posted a new version of Search deList in post #68, now you can use F1 & F2 to narrow or widen the combobox size at run time. The change will be preserved throughout the session (until you close Excel).


----------



## RajK2005 (Nov 17, 2022)

Akuini said:


> You're welcome, glad to help & thanks for the feedback.
> 
> @RajK2005
> In case you're interested, I just posted a new version of Search deList in post #68.



Thank you for the improved version. This works much better and love the continuous mode toggle option.
I see that you have implemented the option to choose multiple values. That's something that helps me a lot. However, there seems to be 2 bugs:

1) When I enter a value and press F9, it starts with comma (,) instead of adding comma after the 1st selection. See below:






Can this be fixed?

2) Once I press ALT+Right Arrow key, it shows the pop-up but clicking on down arrow key after that doesn't show the list and scroll down. You have to enter some input for it to start showing the list or click on the Down Arrow Icon on the box. Pressing Down Arrow Key doesn't start scrolling down. This is unlike the earlier version in which you can scroll down by simply pressing Down Arrow Key.

Also, can we change the separator to something else? I would prefer a | (vertical bar) without space instead of a comma and space to input multiple values.

Thanks again.


----------



## Akuini (Nov 17, 2022)

RajK2005 said:


> I see that you have implemented the option to choose multiple values. That's something that helps me a lot. However, there seems to be 2 bugs:
> 
> 1) When I enter a value and press F9, it starts with comma (,) instead of adding comma after the 1st selection. See below:


Ah, you're right. 
Try replacing "Sub sentValue" (in Userform module) with this one:

```
Sub sentValue()
'insert combobox value into the active cell

Dim tx As String

'If F5flag = True Then F5flag = False: Exit Sub 'if come from F5 key
    
    With Me.ComboBox1
        tx = .Text
        If .ListIndex > -1 Then
                
*                If F9flag = True Then
                    If ActiveCell <> Empty Then tx = ActiveCell & Sprt & tx  'hit F9 to insert mutiple entries
                End If*
                
                If Left(tx, 1) = "0" Then
                    If IsNumeric(tx) Then
                       ActiveCell = "'" & tx    ''insert as text, e.g: "01" will remain "01" instead of "1"
                    Else
                       ActiveCell = tx
                    End If
                Else
                    ActiveCell = tx
                End If
                
       
        ElseIf tx = "" Then
                'do nothing
        Else
                MsgBox "Wrong input", vbCritical
                Exit Sub
        End If
    
    End With
    
                If F9flag Then 'insert mutiple entries mode
                    'do nothing
                ElseIf pF8flag Then 'non-continuous_mode
                    Unload Me
                Else
                    Call continuous_mode
                End If
End Sub
```



RajK2005 said:


> 2) Once I press ALT+Right Arrow key, it shows the pop-up but clicking on down arrow key after that doesn't show the list and scroll down. You have to enter some input for it to start showing the list or click on the Down Arrow Icon on the box. Pressing Down Arrow Key doesn't start scrolling down. This is unlike the earlier version in which you can scroll down by simply pressing Down Arrow Key.


You're right. In earlier version when the userform is activated the listbox will be populated with the list. But it could slow down the process when the list is big, so I chose to populate it when the user do something like entering input or clicking the down arrow button. But you can also do that by *pressing Alt+Down arrow*. So you can activate the userform & show the list in sequence: press ALT and hold it while pressing Right arrow then Down arrow (so no need to press ALT twice).



RajK2005 said:


> Also, can we change the separator to something else? I would prefer a | (vertical bar) without space instead of a comma and space to input multiple values.


At the top of Userform code you can change this part as needed:

```
Private Const Sprt As String = ", "  'separator of multiple entries
```

Thanks so much for the feedback.


----------



## RajK2005 (Nov 17, 2022)

Akuini said:


> Ah, you're right.
> Try replacing "Sub sentValue" (in Userform module) with this one:
> 
> ```
> ...


Thanks. It works now. Great work as usual.

Also, if you don't mind, can I DM you? I have a VBA which works well but breaks down with old excel versions when my clients used it. Also, I think if you take a look, maybe you can help improve it and run a little faster.


----------



## Akuini (Nov 17, 2022)

RajK2005 said:


> Thanks. It works now. Great work as usual.
> 
> Also, if you don't mind, can I DM you? I have a VBA which works well but breaks down with old excel versions when my clients used it. Also, I think if you take a look, maybe you can help improve it and run a little faster.


Actually, you can't take the question off the forum, so just start a new thread.


----------



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

```
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 (Nov 17, 2022)

There was a bug in this new version, regarding inserting multiple entries as described in post #78.
So, here's the revised version:
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
				




Full description about the new version is on post #68 and in the manual.

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


----------



## Akuini (Nov 23, 2022)

abdelfattah said:


> it  doesn't  show  at all .


Do you still have problem with this?
It's probably about security setting, you have to unblock the add-in file first. Check this link:





						Excel: Add-ins do not load
					

jkp-ads.com: Excel Add-ins do not load, how to resolve



					jkp-ads.com
				




And check post #81 for the latest version.


----------



## InfinityC (Dec 5, 2022)

Akuini said:


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


Akuini, very cool add-in tool. I was wondering if there was a way to utilize this to more quickly select Times of Day within a dropdown validation? It appears the search shows the values instead of the times. I want to select from my validation based on the text, but then the value of the cell is later used for a calculation.

5:00 AM​5:15 AM​5:30 AM​5:45 AM​6:00 AM​6:15 AM​6:30 AM​6:45 AM​7:00 AM​7:15 AM​7:30 AM​7:45 AM​8:00 AM​8:15 AM​8:30 AM​8:45 AM​9:00 AM​9:15 AM​9:30 AM​9:45 AM​10:00 AM​10:15 AM​10:30 AM​10:45 AM​11:00 AM​11:15 AM​11:30 AM​11:45 AM​12:00 PM​12:15 PM​12:30 PM​12:45 PM​1:00 PM​1:15 PM​1:30 PM​1:45 PM​2:00 PM​2:15 PM​2:30 PM​2:45 PM​3:00 PM​3:15 PM​3:30 PM​3:45 PM​4:00 PM​4:15 PM​4:30 PM​4:45 PM​5:00 PM​5:15 PM​5:30 PM​5:45 PM​6:00 PM​6:15 PM​6:30 PM​6:45 PM​7:00 PM​7:15 PM​7:30 PM​7:45 PM​8:00 PM​8:15 PM​8:30 PM​8:45 PM​9:00 PM​9:15 PM​9:30 PM​9:45 PM​10:00 PM​10:15 PM​10:30 PM​10:45 PM​11:00 PM​11:15 PM​11:30 PM​11:45 PM​12:00 AM​


----------



## Akuini (Dec 6, 2022)

@InfinityC, welcome to the Forum.
There are 2 issues regarding date or time value, it might change when loaded to combobox & when sent back to cell. That's because the value of the combobox is just text, it doesn't have a date/time format.
I haven't found a way around this problem yet, I'll let you know if I do.

I shared a new version of this add-in, i.e "Search_deList_v2.1", please check post #81.


----------



## InfinityC (Dec 8, 2022)

Thanks Akuini! Again very cool tool. Appreciate the response and other posts in this forum.


----------



## Akuini (Dec 11, 2022)

InfinityC said:


> Akuini, very cool add-in tool. I was wondering if there was a way to utilize this to more quickly select Times of Day within a dropdown validation? It appears the search shows the values instead of the times. I want to select from my validation based on the text, but then the value of the cell is later used for a calculation.


I've amended the code to tackle the the time problem as you described. 

Time problem
If the cell value is time, then in the combobox it will be displayed as number instead of time.
To resolve this problem:
*Press F6* to load the list as *text*. Pick an entry then hit enter (or click).
Caution:
If you load the list as text, then you need to be aware of this kind of problem:
For example:
A cell is formatted as “h.mm AM/PM”, the value displayed in the formula bar is 11.45.33, in the cell it is displayed as 11.45 AM (without the second part, which is 33), when loaded as text in the combobox it’s displayed as 11.45 AM, the second part will be removed, so when it’s sent back to a cell, the value (as displayed in the formula bar) will be 11.45.00 instead of 11.45.33. So, the value is actually changed.
Of course if the second part of the data is actually "00" then that won't be a problem.

Here's the new version, updated 11-Dec-2022.
*Search deList v.2.2 + manual.zip*








						Search deList v.2.2 + 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
				




Please try & provide feedback.


----------



## Akuini (Dec 25, 2022)

forothergaming said:


> So I have to use F9 then ESC intead of enter?


Sorry, in the manual I actually forgot to explain how to do it properly.🙏
You're right, to insert multiple entries, hit *F9 *multiple times as needed and then finish with *Esc *instead of Enter.
And another way: hit F9 multiple times as needed and then hit *Delete *(to empty the combobox) then hit *Enter*.


----------

