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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
5,260
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:

image Search deList.jpg


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.365.1 + manual.zip, it works on Excel 365 or later

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
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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!!!!
 
Upvote 0
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.:)
 
Upvote 0
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
 
Upvote 0
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:

Rich (BB code):
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.
 
Upvote 0
Hi Akuini,
thanks for the help.
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?
 
Upvote 0
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:


Rich (BB code):
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:

 
Upvote 0
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"


Rich (BB code):
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
 
Upvote 0
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
 
Upvote 0
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. (y)
And if you have any other feedback to improve the add-in's functionality, I'd love to hear it.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top