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

 

[charly1]

Hi, I've attached a link of my sample data.

NewCat - Copy (2).xlsm

sheet1 אחרונים,1747,ה' תקז,1823,ה' תקפג,76 מס',תוך ספר,עמוד',מדף,BOOK Name,חל',נושא כללי: בנושאים,נושא פרטי: בנושאים,נושא: בספרים,נושא כללי: בנושאים [שני],נושא פרטי: בנושאים [שני],נושא: בספרים [שני],תוכן,Artists 1,,,,מעלות התורה,0,מחשבה_ומוסר,-,מחשבה_ומוסר,-,-,-,-,ר' אברהם בן שלמה זלמן [אחי הגר"...

docs.google.com

Sheet one is a table of books with various properties in the adjoining cells. The book names are listed in Column E (sheet 1) and the artists names are in that same sheet in column N. Also in Sheet 2 I have a separate table which features just the artist names with their dates of birth and death in the adjoining columns. [Please note that the artist column in sheet one is much longer that the artist column in sheet 2. The reason for this is that in sheet 1 which is the book list an artist who wrote more than one book will feature twice, and in sheet two the artist list features just one of every name.

What I would want to do is to have a two tab user form on a separate sheet: in the first tab there would be a searchable combobox featuring a list of all the book names. When a specific book would be selected, various text boxes in the user form would then display specific data for that book that have been retrieved from the table. In the second tab of the user form I would want a combo box of the artist names, and on selection of a name a list of all the books he wrote [from in my table] would appear in the user form side by side with their corresponding data.

Quite understandably i didn't think i could post an entire project, and thought i would try and work on it myself, beginning with the searchable combo box.

Note: Considering that the artist list in sheet 1 features more than one of each name [if he wrote more than one book], in the combo search box of artist names I would need to reference sheet two and pull all the corresponding book data from sheet 1.

Sorry For driving you crazy, although you cannot imagine how much your add on has already helped me!

 

[Akuini]

Hi, I've attached a link of my sample data.

Ok, you need to start a new thread for your question because actually the topic of this thread is different.

As for the searchable combobox (in a Userform), here's the example:
Akuini - Userform Combobox - Fast Live Search on large data

I have created a searchable combobox (in a Userform) to enable fast Live Search on large data (tested on 100K data rows). By 'live search,' I mean that the results are instantly displayed after typing each character in the combobox.

Here's how it works: Type some keywords in the combobox, separated by a space (e.g., "ma la"), and the list in the combobox will narrow down as you type. The search ignores the order of the keywords, so typing "ma la" will match both "Maryland" and "Alabama."

The search results will be displayed after you type the second character. The reason behind this is to speed up the search. Searching with only one character could return too many results and cause noticeable lag, especially on large data sets. However, searching with two characters or more will be faster.

 

[charly1]

Thanks a million. You're the greatest.

 

[Akuini]

Thanks a million. You're the greatest.

You're welcome.

 

[charly1]

Hi again
I've been using your searchable combo box, and its been saving me loads of time. Thanks.
I would like to have two of your searchable combo boxes in the same user form with all the same coding, except that they should each have different source data range. Is there an easy way to do accomplish this?

Thanks so much

 

[Akuini]

@charly1

I would like to have two of your searchable combo boxes in the same user form with all the same coding, except that they should each have different source data range. Is there an easy way to do accomplish this?

Ok, here's an example:

Akuini - double combobox - Fast Live Search, userform combobox, no sort, no limit, start at n char #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

Note: if your data isn't too large then you can set CN1 and CN2 to 0, as I did in this example, so the list will show up even if the combobox is blank.

 

[charly1]

Thanks a lot. You're the best.

 

[Akuini]

Thanks a lot. You're the best.

You're welcome.

 

[charly1]

Final question (hopefully)!

I am putting you're "search deList" add in to good use, and have got it setup to open on double click.

I also need to change quite often between multiple entry and non multiple entry (F9). Is there a possibility of having a more handy way of switching between multiple and non multiple entry, e.g having a tiny clickable option button on the add-in popup next to the question mark, or at least be able to make a short cut key on the keyboard [a more handy one than F9]?

 

[Akuini]

I also need to change quite often between multiple entry and non multiple entry (F9). Is there a possibility of having a more handy way of switching between multiple and non multiple entry, e.g having a tiny clickable option button on the add-in popup next to the question mark, or at least be able to make a short cut key on the keyboard [a more handy one than F9]?

How about using Shift+Enter? or what's your suggestion?
Which search deList version are you using?