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

 

[craig_toohey]

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]

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]

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]

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]

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:

1. Open vba window
2. Open VBAProject Search_deList_v1.xlam
3. Open UseForm1 code window
4. 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]

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]

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:

1. Open vba window
2. Open VBAProject Search_deList_v1.xlam
3. Open UseForm1 code window
4. 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]

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

1. Open vba window
2. Open VBAProject Search_deList_v1.xlam
3. Open UseForm1 code window
4. 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.

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]

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]

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