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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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 “, ”).
Unfotunely they're not really inserted, only one was in it when I press enter. Could you fix it please?
 
Upvote 0
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.
I just posted a new version of Search deList in post #68, now you can 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).
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)

@RajK2005
In case you're interested, I just posted a new version of Search deList in post #68.

Thank you for the improved version. This works much better and love the continuous mode toggle option.
I see that you have implemented the option to choose multiple values. That's something that helps me a lot. However, there seems to be 2 bugs:

1) When I enter a value and press F9, it starts with comma (,) instead of adding comma after the 1st selection. See below:

1668673126522.png



Can this be fixed?

2) Once I press ALT+Right Arrow key, it shows the pop-up but clicking on down arrow key after that doesn't show the list and scroll down. You have to enter some input for it to start showing the list or click on the Down Arrow Icon on the box. Pressing Down Arrow Key doesn't start scrolling down. This is unlike the earlier version in which you can scroll down by simply pressing Down Arrow Key.

Also, can we change the separator to something else? I would prefer a | (vertical bar) without space instead of a comma and space to input multiple values.

Thanks again.
 
Upvote 0
I see that you have implemented the option to choose multiple values. That's something that helps me a lot. However, there seems to be 2 bugs:

1) When I enter a value and press F9, it starts with comma (,) instead of adding comma after the 1st selection. See below:
Ah, you're right.
Try replacing "Sub sentValue" (in Userform module) with this one:
Rich (BB code):
Sub sentValue()
'insert combobox value into the active cell

Dim tx As String

'If F5flag = True Then F5flag = False: Exit Sub 'if come from F5 key
    
    With Me.ComboBox1
        tx = .Text
        If .ListIndex > -1 Then
                
                If F9flag = True Then
                    If ActiveCell <> Empty Then tx = ActiveCell & Sprt & tx  'hit F9 to insert mutiple entries
                End If
                
                If Left(tx, 1) = "0" Then
                    If IsNumeric(tx) Then
                       ActiveCell = "'" & tx    ''insert as text, e.g: "01" will remain "01" instead of "1"
                    Else
                       ActiveCell = tx
                    End If
                Else
                    ActiveCell = tx
                End If
                
       
        ElseIf tx = "" Then
                'do nothing
        Else
                MsgBox "Wrong input", vbCritical
                Exit Sub
        End If
    
    End With
    
                If F9flag Then 'insert mutiple entries mode
                    'do nothing
                ElseIf pF8flag Then 'non-continuous_mode
                    Unload Me
                Else
                    Call continuous_mode
                End If
End Sub

2) Once I press ALT+Right Arrow key, it shows the pop-up but clicking on down arrow key after that doesn't show the list and scroll down. You have to enter some input for it to start showing the list or click on the Down Arrow Icon on the box. Pressing Down Arrow Key doesn't start scrolling down. This is unlike the earlier version in which you can scroll down by simply pressing Down Arrow Key.
You're right. In earlier version when the userform is activated the listbox will be populated with the list. But it could slow down the process when the list is big, so I chose to populate it when the user do something like entering input or clicking the down arrow button. But you can also do that by pressing Alt+Down arrow. So you can activate the userform & show the list in sequence: press ALT and hold it while pressing Right arrow then Down arrow (so no need to press ALT twice).

Also, can we change the separator to something else? I would prefer a | (vertical bar) without space instead of a comma and space to input multiple values.
At the top of Userform code you can change this part as needed:
VBA Code:
Private Const Sprt As String = ", "  'separator of multiple entries

Thanks so much for the feedback. (y)
 
Upvote 0
Ah, you're right.
Try replacing "Sub sentValue" (in Userform module) with this one:
Rich (BB code):
Sub sentValue()
'insert combobox value into the active cell

Dim tx As String

'If F5flag = True Then F5flag = False: Exit Sub 'if come from F5 key
  
    With Me.ComboBox1
        tx = .Text
        If .ListIndex > -1 Then
              
                If F9flag = True Then
                    If ActiveCell <> Empty Then tx = ActiveCell & Sprt & tx  'hit F9 to insert mutiple entries
                End If
              
                If Left(tx, 1) = "0" Then
                    If IsNumeric(tx) Then
                       ActiveCell = "'" & tx    ''insert as text, e.g: "01" will remain "01" instead of "1"
                    Else
                       ActiveCell = tx
                    End If
                Else
                    ActiveCell = tx
                End If
              
     
        ElseIf tx = "" Then
                'do nothing
        Else
                MsgBox "Wrong input", vbCritical
                Exit Sub
        End If
  
    End With
  
                If F9flag Then 'insert mutiple entries mode
                    'do nothing
                ElseIf pF8flag Then 'non-continuous_mode
                    Unload Me
                Else
                    Call continuous_mode
                End If
End Sub


You're right. In earlier version when the userform is activated the listbox will be populated with the list. But it could slow down the process when the list is big, so I chose to populate it when the user do something like entering input or clicking the down arrow button. But you can also do that by pressing Alt+Down arrow. So you can activate the userform & show the list in sequence: press ALT and hold it while pressing Right arrow then Down arrow (so no need to press ALT twice).


At the top of Userform code you can change this part as needed:
VBA Code:
Private Const Sprt As String = ", "  'separator of multiple entries

Thanks so much for the feedback. (y)
Thanks. It works now. Great work as usual.

Also, if you don't mind, can I DM you? I have a VBA which works well but breaks down with old excel versions when my clients used it. Also, I think if you take a look, maybe you can help improve it and run a little faster.
 
Upvote 0
Thanks. It works now. Great work as usual.

Also, if you don't mind, can I DM you? I have a VBA which works well but breaks down with old excel versions when my clients used it. Also, I think if you take a look, maybe you can help improve it and run a little faster.
Actually, you can't take the question off the forum, so just start a new thread.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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