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

[Akuini]

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

How it works:

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

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

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

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

Image:

How to use it:

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

NOTES:

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

Search_deList_v1 add-in:
Search_deList_v1

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

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

Search deList v.2.1 + manual

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

www.mediafire.com

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

Search deList v.365.1.1 + manual

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

www.mediafire.com

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

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

Regards,
Akuini

 

[Stez84]

Good morning all,
@Akuini Thank you for your job is really much appreciated!
I've been search for an add-in like yours for a while! I'm working for a company with a lot of 2007 version still installed in his computers and this is really slowing down the data entry's process.
I tried to install your tool but unfortunately when trying to press ALT + RIGHT ARROW, VBA pop up with an error (pic attached below). Any idea of what can be the problem?

Error is in Italian, translated is: type defined by user not defined

 

[RoryA]

Change that line to:

#If VBA7 Then
Dim hdc As LongPtr
#Else
Dim hdc as Long
#End If

 

[Stez84]

Thanks @RoryA it was easier than I thought.
Is it possible to move the userform below the cell and not on its right?

 

[RoryA]

Honestly no idea - I haven't looked at the add-in since I don't need it.

 

[Akuini]

Change that line to:

#If VBA7 Then
Dim hdc As LongPtr
#Else
Dim hdc as Long
#End If

Thanks for the input; I really appreciate it.

 

[Akuini]

Is it possible to move the userform below the cell and not on its right?

Try this:
1. In xDAV_1 module, replace Public Sub GetPointCoordinates with this one:

Public Sub GetPointCoordinates(ByVal cellrange As Range, ByRef pointcoordinates As pointcoordinatestype)
  Dim i As Long
    ConvertUnits
    Set cellrange = cellrange.MergeArea.Offset(, 1)
    For i = 1 To ActiveWindow.Panes.Count
        If Not Intersect(cellrange, ActiveWindow.Panes(i).VisibleRange) Is Nothing Then
            'pointcoordinates.Left = PixelsToPointsX(ActiveWindow.Panes(i).PointsToScreenPixelsX(cellrange.Left))
            pointcoordinates.Left = PixelsToPointsX(ActiveWindow.Panes(i).PointsToScreenPixelsX(ActiveCell.Left))
            pointcoordinates.Top = PixelsToPointsY(ActiveWindow.Panes(i).PointsToScreenPixelsY(cellrange.Top))
            pointcoordinates.Right = pointcoordinates.Left + cellrange.Width * zoomratio
            pointcoordinates.Bottom = pointcoordinates.Top + cellrange.Height * zoomratio
            Exit Sub
        End If
    Next
End Sub

2. In Userform1 module, replace Sub set_Position with this one:

Sub set_Position()
'this code to place the userform base on active cell position is from Yin Cognyto
'https://stackoverflow.com/questions/41884148/how-do-i-align-a-userform-next-to-the-active-cell

  Dim pointcoordinates As pointcoordinatestype, horizontaloffsetinpoints As Double, verticaloffsetinpoints As Double
    With Me

        horizontaloffsetinpoints = (.Width - .InsideWidth) / 2
        verticaloffsetinpoints = 6
        Call GetPointCoordinates(ActiveCell, pointcoordinates)
        .StartUpPosition = 0

'        .Top = pointcoordinates.Top - verticaloffsetinpoints
'        .Left = pointcoordinates.Left - horizontaloffsetinpoints + 1

        .Top = pointcoordinates.Bottom
        .Left = pointcoordinates.Left

    End With
End Sub

 

[Stez84]

Try this:
1. In xDAV_1 module, replace Public Sub GetPointCoordinates with this one:

Public Sub GetPointCoordinates(ByVal cellrange As Range, ByRef pointcoordinates As pointcoordinatestype)
  Dim i As Long
    ConvertUnits
    Set cellrange = cellrange.MergeArea.Offset(, 1)
    For i = 1 To ActiveWindow.Panes.Count
        If Not Intersect(cellrange, ActiveWindow.Panes(i).VisibleRange) Is Nothing Then
            'pointcoordinates.Left = PixelsToPointsX(ActiveWindow.Panes(i).PointsToScreenPixelsX(cellrange.Left))
            pointcoordinates.Left = PixelsToPointsX(ActiveWindow.Panes(i).PointsToScreenPixelsX(ActiveCell.Left))
            pointcoordinates.Top = PixelsToPointsY(ActiveWindow.Panes(i).PointsToScreenPixelsY(cellrange.Top))
            pointcoordinates.Right = pointcoordinates.Left + cellrange.Width * zoomratio
            pointcoordinates.Bottom = pointcoordinates.Top + cellrange.Height * zoomratio
            Exit Sub
        End If
    Next
End Sub

2. In Userform1 module, replace Sub set_Position with this one:

Sub set_Position()
'this code to place the userform base on active cell position is from Yin Cognyto
'https://stackoverflow.com/questions/41884148/how-do-i-align-a-userform-next-to-the-active-cell

  Dim pointcoordinates As pointcoordinatestype, horizontaloffsetinpoints As Double, verticaloffsetinpoints As Double
    With Me

        horizontaloffsetinpoints = (.Width - .InsideWidth) / 2
        verticaloffsetinpoints = 6
        Call GetPointCoordinates(ActiveCell, pointcoordinates)
        .StartUpPosition = 0

'        .Top = pointcoordinates.Top - verticaloffsetinpoints
'        .Left = pointcoordinates.Left - horizontaloffsetinpoints + 1

        .Top = pointcoordinates.Bottom
        .Left = pointcoordinates.Left

    End With
End Sub

Thank you! The update works perfectly fine!!

 

[Akuini]

Thank you! The update works perfectly fine!!

You're welcome.

 

[darksider_hp9x]

@Akuini Thank for add-in great! Thanks for your help!
I have some errors that I don't know how to fix. I'm not good at vba

1. My cell contains more than 256 characters. When I enter it, I get an error as shown in the picture

2. When my cell contains a "~" or Alt+Enter character, it gives the same error as when I enter and the combobox is empty

 

[Akuini]

I have some errors that I don't know how to fix. I'm not good at vba

Which Search deList version are you using?