+ Reply to Thread
Results 1 to 1 of 1

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

  1. #1
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    971

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

    I created an add-in called “Search deList”, and I want to 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 some keywords 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, 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.



    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:
    Copy to clipboard
    Please Login or Register  to view this content.
    Now, in every sheet, double-clicking any cell that has data validation (with List type) will open the Userform.

    How to test it:
    Install the add-in. This is how: https://exceloffthegrid.com/install-...all-excel-add/
    Open any workbook that has data validation (with list type).
    In any cell that has data validation, pressing ALT+RIGHT will open a Userform with a combobox.
    Play with it & see how it works.

    NOTES:
    • This add-in also works for dependent data validation.
    • 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.


    There are 2 versions:
    1. Search_deList_v1
    It should work on Excel 2007 or later.
    2. Search_deList_v365
    It should work on Excel 365 or later that has dynamic array function. This version is faster than v_1, especially when you have a long list, say more than 20K items.

    Regards,
    Akuini
    Attached Images Attached Images  
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-22-2021, 04:21 AM
  2. Searchable Data Validation list
    By asad.ermo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2020, 04:20 AM
  3. [SOLVED] VBA Possibly: Searchable list while using data validation through a named range in a table
    By AMoreno in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 10-03-2019, 10:47 AM
  4. Searchable Data Validation Lists
    By MrExcel1991 in forum Excel General
    Replies: 1
    Last Post: 09-14-2018, 12:57 PM
  5. create a searchable excel document
    By jeffpanagos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2013, 10:28 PM
  6. Create a searchable data sheet
    By danishaikh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-15-2012, 11:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1