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

 

[Akuini]

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

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.

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

 

[Akuini]

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]

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

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

thanks

@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

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

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:

View attachment 52701

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

Regards,
Akuini

Hi Akuini

The download from mediafire is not working

 

[Akuini]

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]

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

 

[Akuini]

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]

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]

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