How to Search Data Validation Drop-down Lists in Excel - Excel Campus
121

How to Search Data Validation Drop-down Lists in Excel

Bottom line: Learn a fast and easy way to search any data validation list or in-cell drop-down list with a free tool.

Skill level: Beginner

how-to-search-data-validation-lists-in-excel-cover-640x360

Data validation lists are a great way to control the values that are input in a cell.  These drop-down lists also allow us to choose options that can drive financial models, reports, or dashboards.

However, there is no built-in way to search the validation list in Excel.  It can be difficult to scroll through these lists when the drop-down contains a lot of items.  There are some really cool formula based solutions to this problem, but they require a lot of setup work for each validation list in your file.

no-built-in-way-to-search-data-validation-drop-down-lists-in-excel

So I developed a very simple add-in that helps solve this problem…

Video Overview of List Search

Download the Free List Search Add-in

Click the links below to jump down to the feature update videos.

Search Validation Lists with List Search

The List Search Add-in makes it fast and easy to search any validation list.  It also works with lists of data that do not contain data validation cells.

List Search Add-in Drop-down List Contains Data Validation List of Items

The List Search form contains a drop-down box that loads the selected cell’s validation list.  The drop-down box also functions as a search box.  You can type a search in the box and the results will be narrowed down as you type.  This is a Google-like search and the results will include any item that contains the search term.  The item does not have to start with the search term.

list-search-results-narrowed-down-as-you-type-data-validation-list

Once you have selected the item you are looking for, press Enter on the keyboard or press the Input Value button on the form to input the value in the selected cell.

enter-key-or-input-value-button-to-enter-value-in-cell-list-search

List Search works on any cell in any workbook.  There is NO special setup required.  Simply select a cell, press the List Search button, and start searching the list.

List Search Options & Features

The List Search Add-in contains some features that make it super fast to do data entry and work with your lists.  Press the Menu button in the List Search window to see the options.

list-search-add-in-options-sort-and-direction-settings

  • Select Next Cell – After pressing the Enter key or Input Value button, the cell below the active cell is selected.  This behavior can be changed in the direction drop-down menu.
    • Down – selects the cell below the active cell.
    • Right – selects the cell to the right of the active cell.
    • None – does not change the selection.
    • Close – closes the List Search window.
    • Paste – Copies the input value to the clipboard and pastes it to the active cell using the VBA SendKeys method.  The List Search Window closes.  This is the only option that retains the undo history in Excel.
  • Sort Order – The drop-down list can be sorted in ascending (A-Z), descending (Z-A), or original order by pressing the toggle buttons in the options menu.  This only sorts the list in the List Search window.  It does not sort the data validation list in the cell.
  • List Info – The Info button displays additional information about the drop-down list.  It currently displays the total number of items in the list.
  • Create List of Unique Values – A new button has been added that copies the contents of the drop-down list to the clipboard.  You can then paste the list to any range in the workbook.  This is a fast way to create a list of unique values when you use List Search on a cell that does NOT contain data validation.  You can also filter the list by typing a search, then copy the filtered list to the clipboard.

IMPORTANT Note: When inputting values to the active cell, the only way to retain the Undo History is by using the Paste option in the Select Next Cell drop-down list.  List Search uses macros to input the selected value, and macros typically clear the undo history in Excel when they modify the workbook.  The Paste option is a workaround that uses the SendKeys method to copy and paste the selected value.  This mimics what the user would do to copy/paste, and does NOT clear the Undo history in Excel.

Works on Lists Without Data Validation

List Search works on cells that do not contain data validation too.  If you select a cell that does NOT contain data validation and open List Search, the drop-down will be loaded with a list of unique items from the column of the selected cell.

list-search-works-on-cells-that-do-not-contain-validation

This is similar to pressing Alt+Down Arrow in a cell to see a list of values in that column.  However, the list does not need to be contiguous.  Even if the column contains blanks, List Search will still load all the unique values in the current data region or list.

November 2016 Update

I published an updated version of the List Search Add-in with a few new features.  Here is a video overview of the new features.

Here is a list of the features.

  1. Added a “Paste” option to the directions list.  This will copy the input value to the clipboard and paste it to the activecell.  The Paste options uses the SendKeys method in VBA to perform the paste.  This means the Undo history will NOT be cleared when using the Paste Option.
  2. Settings for the Options Menu and Input Direction Drop-down are now saved to the registry.  Your preferences will be saved and loaded when you open Excel and the add-in again in the future.
  3. Added enhancements for Excel Tables. When the activecell is in a Table and the cell does not contain validation, a unique list of values will be loaded and exclude the Table headers and total row.
  4. Added Copy List feature that copies the contents of the drop-down list to the clipboard. This feature is used to create a list of unique values from a column/table when the activecell does not contain validation. It also works when the list is filtered with a search term to only copy filtered results.

April 2017 Update

Based on your awesome feedback and requests, I’m excited to publish another update with new features.  I share the new features in the following video.

Here is a list of the new features in the April 2017 update.

  1. Added the Auto Open feature to automatically open the form when a cell that contains data validation is selected. This option can be toggled on/off with a toggle button in the options menu.
  2.  The add-in now works with data validation created by formulas (OFFSET & INDEX) and comma separated lists.  It should work with all types of data validation lists.
  3. Updated Escape Key behavior to close the List Search window.  If there is text in the search box, then Escape clears the search box.  If the search box is empty, then Escape closes the form.

In the video I also showed some cells with drop-down button icons next to them, even though the cell was not selected.  Check out my article on how to make the validation list drop-down buttons always visible to learn more about this technique.

Download the List Search Add-in (it’s Free!)

The List Search Add-in is free to download and use.  The VBA code is also open source so you can modify it for your needs.  This is also a great way to learn how macros and add-ins work if you are learning VBA.

List Search Add-in Free Download 640x360

Click here to download the List Search Add-in

Note: You will create a free account for the Excel Campus Members site to access the download and any future updates.

The download site also contains installation instructions and videos.

How Can My Co-workers Use List Search?

The List Search Add-in is installed on your computer, and only you will be able to see the XL Campus tab and use List Search.  If you want your co-workers to be able to use List Search there are two ways to go about it.

  1. Send them a link to this page to download and install List Search on their computer.  They will be able to use List Search on any Excel file they have open on their computer.
  2. Import the List Search userform to the VB Project in your Excel file.  You can add the List Search form to any of your workbooks.  This must be a macro enabled workbook.  You will also need to create or import the code module that contains the macro to open the List Search userform.  Then add a button to the worksheet or ribbon that opens the form.

How Can We Make List Search Better?

I hope the List Search Add-in saves you some time searching data validation lists.  The ultimate goal is to make it faster to find the value we are looking for in long lists of data.  Please leave a comment below with any questions or suggestions.  Thank you! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 121 comments
Joseph - June 23, 2017

Great Tool! I am starting to develop my own add-in to house all the macros I created for my colleagues, and I would love to add your list search as a part of mine or add my code to your add in, so they only have to install one and it’s all together on the ribbon. I am a beginner when it comes to add-ins, and I can’t seem to figure out how to move it between them. Is this possible?

Reply
rosalind tjioe - May 25, 2017

Hi Jon,
Thank you for the add in!
I managed to see the ribbon for the “List search” Add In after trusting the location.
But all I can see is the “List Search” Icon and Help page.
I don’t see any other buttons. Am I missing something?

In addition, when I click on a cell that is blank or has no validation list, there is a pop up saying “Please select a cell that is not blank or contains a validation list”

However if I make the column contain a valdiation list and click “List Search” there is another error message “Run Time error “13” Type Mismatch” WhenI click Debug, it brings me to the form “f_ListSearch.Show”

thank you!

Reply
    Crystal - June 15, 2017

    I have the same issue! Did you find a fix for it yet?

    Reply
shakir ansari - May 19, 2017

Hi Sir

I want know how we can split a single cell data into two different column
like 5days 5 days

Reply
Gavin - May 3, 2017

that is an incredibly good tip and utility talk. What would make it even more useful would be if this could be used for pivot table selections e.g. selection could be made from using the search functionality and pass through to the pivot table. supercharged this would be if it could pass multiple selections based on a list in say another table

Your thoughts?

Reply
    Jon Acampora - June 1, 2017

    Thanks for the suggestions Gavin! I like the idea for using List Search on the Filters Area of the pivot table. I will add that to the feature list for the future.

    In regards to multiple selections, I just posted a blog post and video on how to filter for a list of items. The COUNTIF technique can be used to add a column to the source data, then add that column to the Filters Area or slicer to filter for a list of items. It’s a bit of a workaround for what you are looking for but should work. I like your idea though. I will give it more thought.

    Reply
Matteo - April 28, 2017

BEST ONE !!!

Reply
Phylipe Schad - April 28, 2017

Very cool add-in. I’ve always had trouble searching lists, it will save me a lot of time.

Thank you

Reply
RON - April 27, 2017

Best site for information and extended education relating to Excel!

Reply
Bill - April 26, 2017

A fantastic addin! I really enjoy!

Can you list search in a column in another workbook? If so, how? Thanks

Reply
    Jon Acampora - May 25, 2017

    Thank you Bill! You can’t search in other workbooks yet, but I have that as a feature for the future. I’ve had a few requests for it.

    Reply
PeeZy - April 26, 2017

Hi Jon Linked removed ?

https://members.excelcampus.com/store/M25zbyjw

how to download list search add in ?

sry for my bad english i try to typing now haha

Reply
Andrew - April 11, 2017

I should add that the Run-time error 1004 is on line 3 of the following Sub:

Sub btnListSearch_onAction(control As IRibbonControl)
‘Callback for ListSearch onAction
f_ListSearch.Show ‘(Run-time error 1004 error here)
End Sub

–Andrew

Reply
Andrew - April 11, 2017

Hi Jon,

I downloaded and really like your plug-in, but receive the following error when I run it on a cell that contains data validation rules (it works on all other cells in the same sheet):

Run-time error ‘1004’: Method ‘Range’ of object ‘_Global’ failed.

I tried moving the form and modules to my particular workbook, but that didn’t help.

My data validation is structured as follows: Sheet2 has the data validation values in columns, with the header row in each column specifying a category name, and the values in the remainder of the column specifying the valid values when that category is chosen. In Sheet1, column B allows the user to select a category, which is restricted to the column header categories in Sheet2. When the category is selected in column B of Sheet 1, the values in column A in the same row are restricted to only the validated values for that category specified by the list in Sheet 2. Each validation list is <250 items, with only about 15 categories total.

I'm wondering if having the validation split between two sheets, or using named ranges in my data validation is interfering with your otherwise wonderful plug-in. I also have some very rudimentary VBA code in the workbook that duplicates the template sheet described above based on input from a userform, and clears the Sheet 1 column A value when the column B category is changed. I don't know if that would interfere with your plug-in either. Could you advise? Thank you for any help you can provide, and for the plug-in!

–Andrew

Reply
    Jon Acampora - April 18, 2017

    Hi Andrew,

    Thank you for reporting this. Yes, there is currently an error with validation lists that are created with formulas. I’m working on updating the add-in to work with these cells as well. I will send an email out when it has been updated. Thanks!

    Reply
      Andrew - April 19, 2017

      Thank you for your response, Jon! I would love it if you contacted me when you have a solution for validation lists created with formulas. I am using a controlled vocabulary to manage user inputs, and a few of the validated lists are very long, so a find-as-you-type feature would save time. Additionally, Excel disables mouse wheel scrolling in the lists, and also the ability to exclude blank values, which are generated by having a blanket cell range point to the validated list so I don’t have to update it each time items are added. Those limitations in Excel make your plug-in particularly useful.

      –Andrew

      Reply
Frank Colon - April 10, 2017

Jon,

I’d love to be able to point your tool to a list in one sheet, then work on another sheet, where I can leverage the list from the first sheet. How difficult would it be to provide a named range to f_ListSearch, from which list items could be searched, selected and inserted in a cell from a completely different sheet. Also, it might be useful to allow multiple items to be selected and dropped in a cell, separated by some preferred separator. That’s my list for Santa. Btw, your hear this a lot from others, that your tool is terrific. That praise is rightfully earned. I’m hoping my suggestions are viable and someday find their way into the tool. It would catapult, an already exemplary tool, to a much higher level.

Reply
    Jon Acampora - April 18, 2017

    Hi Frank,
    My apologies for not responding sooner. Thank you so much for the feedback and suggestions. Those are some really great ideas! Both are definitely possible. I will put them on the list for future features.

    Thanks again for your support! 🙂

    Reply
Stuart - March 22, 2017

Hello Jon,

I must be missing something but when I add the add-in to my cart, go to my library and click on it there appears to be no way to download it?

Reply
    Jon Acampora - March 29, 2017

    Hi Stuart,
    You might want to try refreshing the page. You should see a link to the Download page towards the bottom of the page. Here is a screenshot.

    List Search Add-in Download Page

    Please let me know if you have any questions. Thanks!

    Reply
Jose Luis Prieto - March 12, 2017

Thanks for your add-in, it’s really helpful. In my case the add-in works perfect except when the validation list is defined using the “INDIRECT” function:
Origin=INDIRECT(“Name”)

I’m Spanish and I have excel 2010 installed in Spanish. Could be this the reason of the failure(Spanish function for “INDIRECT” is “INDIRECTO”?

Thanks

Reply
    Jon Acampora - March 20, 2017

    Hi Jose,
    Thank you! I’m happy to hear the add-in is helping you. I need to update it to work with cells that use formulas in the validation. It does not currently work with any formulas. I will send out an email when the add-in has been updated. Thanks!

    Reply
      Simon - March 30, 2017

      Hi Jon, Jose,
      I had the same problem, however if you build an array in a spare column using the indirect function and then define the validation list on this column then the add-in works. Thanks very much for your help Jon!

      Reply
Leon - February 23, 2017

Hi Jon,

is there a way to call the userform whenever the cell with a validation list is selected?

Thank you.

Reply
    Jon Acampora - February 28, 2017

    Hi Leon,
    Yes, this is possible. I have it on my list of features to add to the add-in. We need to use AppEvents to monitor the cell selection event in any workbook with the add-in.

    You could also move the userform and code modules into a specific workbook, then use workbook events to open the userform when a cell with data validation is selected. I will create an article/video with instructions on this in the future.

    Thanks!

    Reply
Nelson - February 23, 2017

Hi Jon,

Thank you so much for this amazing tool! You have wonderful skills my friend.

II do have a question or two. I’m looking to embed the tool in the workbook so that it can used by various colleagues of mine. The worksheet i have has over +50 cells that have data validation lists attached to it, mostly for the purpose of inputting information and saving it to another woksheet (my version of a mini-db). I do however have only one cell that has a data validation list that’s referenced from the Db itself and is used as a “search” function to pull information from the Db.

I’d like to use your tool so that my colleagues can search each of the +50 input cells by entering keyword searches, including in the “search” cell. Is this possible without having to do a command button and selecting each cell that contains a data validation list manually??? I’d love for my colleagues to just directly start typing in the actual cell that contains the data validation list and have the proper information show up, and able to select it.

If it’s possible, I’d be the office hero! 🙂

Thanks for all your help!!

Reply
    Jon Acampora - February 28, 2017

    Hi Nelson,

    Yes this is possible by copying the userform and code modules to your workbook, then using Workbook Events to open the userform (List Search Add-in window) when the user selects a cell that contains data validation. I have received quite a few requests for this and will create a video on it.

    Reply
Sanh - February 13, 2017

Is it possible to change the location of the add-in.

It keeps appearing in front of my cell.

If me middle of screen would be perfect.

Thanks

Reply
    Jon Acampora - February 15, 2017

    Hi Sanh,
    Yes, the add-in attempts to open next to the active cell, but there are a lot of scenarios where this can go wrong. I have this on my list of features to fix/add.

    You can modify the VBA code if you are familiar with VBA. On the forms Initialize event you can comment out the following line and add a line to change the startup position to 1 (the center of the screen).

    'Call Move_Form
    Me.StartUpPosition = 1
    

    I hope that helps.

    Reply
Mohamed - February 13, 2017

Hi Jon,
Great work and great help, but unfortunately the download is not available!!

Regards
Mohamed.

Reply
Giulio - February 9, 2017

I add the add-in to my workbook but every time I re-open the workbook I have to reload the add-in? Is there a way around this? Thanks

Reply
    Jon Acampora - February 15, 2017

    Hi Giulio,
    Yes, this is due to a Microsoft Office Security Update and can be fixed. Here is an article that explains the disappearing add-ins in more detail.. There are also instructions on the installation instructions included with the add-in. I hope that helps.

    Reply
Nicolas - February 4, 2017

Thank you for that great add-in, you really helpped me alot, I’m very glad that I found it. This uitlity helped me to diminish the size of my excel sheet by 90%.

I’ve one question please, when I press enter to insert the searched value into the selected sheet, the Numlock is turning off automatically.

I’m using MS. Excel 2010 32 bit on windows 7 pro 64 bit.

Thank you once again,
Best Regards,

Reply
    Jon Acampora - February 4, 2017

    Hi Nicolas,

    Thank you for the nice feedback. I’m so happy to hear that the add-in is helping you.

    The NumLock issue is due to a bug with the SendKeys method that is used to copy/paste the value in the sheet. I tried to account for this in the code, but looks like it might not be working for you.

    I just added a new file to the download page that is a NumLock Issue Version. I just removed the line of code that normally turns the Num Lock back on. Give this version a try. There is also a page in the members area on how to update an add-in. You can follow these instructions to install this new version.

    Let me know if that fixes it. Thanks again! 🙂

    Reply
      Nicolas - February 5, 2017

      Hi Jon,

      I didn’t find the file under downloads, but I read the code and did the following amendments:

      Was: Application.SendKeys “^v{numlock}”
      Now: Application.SendKeys “^v”

      And it worked fine, is that correct?

      Reply
Rena - January 25, 2017

Oh, I forgot to mention that there is a worksheet titled “Drop Downs” with multiple lists on it. Two of those lists are used on 10-12 of the worksheets and the rest are used by the individual department/division’s worksheet.

Reply
Rena - January 25, 2017

Great job!!! This is what I’ve been trying to do with a spreadsheet used by many different users. I’m not sure if they use shared computers or if each person has their own laptop. Is there a way to incorporate the list search without having to set macro to enabled? Company policy prohibits having the settings to enable macro and management does not like add-on without it going through the IT department for approval.

Reply
    Jon Acampora - February 4, 2017

    Hi Rena,

    For the add-in, each user will need to have it installed on their computer. The userform can be added to a macro enabled workbook, and it would travel with the workbook. Either way, you will have to enable macros to use the add-in. It is written with VBA macros. I hope that helps.

    Reply
Horatiu ROTAR - January 18, 2017

Hi Jon,

really great tool! Very, very helpful for me. Thank you!
One question: it si possible to take in consideration diacritics when you are searching?

Reply
    Jon Acampora - January 20, 2017

    Thank you Horatiu! I believe this might be possible by using the StrConv function to convert the search text to unicode characters. I have not tested though.

    Reply
Shujuan - January 13, 2017

Thanks for all of nice and clear guidance.

Reply
Saral Adhikari - December 31, 2016

Can you please upgrade this xl campus to highlight the cell whose data we select

Reply
Simon - December 20, 2016

Hello
everything is working great but “paste” doesn’t work for me when i hit enter i get this error message
http://i.imgur.com/jGsMBkW.png

Reply
    Jon Acampora - December 20, 2016

    Hi Simon,
    I’m sorry to hear that. Thanks for sending the screenshot. Which version of Excel are you using? Are you using Excel on a terminal server or any kind of shared environment? Thanks

    Reply
      Simon - December 20, 2016

      Sorry i forget to add this info i’m using windows 7 64 bit and office 2010 64 bit too.
      i’m using it in my laptop

      Reply
        Jon Acampora - December 27, 2016

        Hi Simon,

        Thank you for sending the screenshot. It looks like you might have an older version of the add-in. I updated it on December 4th with code that should work with 64-bit. I’ve tested it on Windows 10 and it does work on 64-bit there. You can download the updated version by logging in to your account and going to the Download page for List Search. Here is the link.

        https://members.excelcampus.com/library

        There are also instructions there on how to update an add-in. Here is a link to that page.

        https://members.excelcampus.com/products/the-list-search-add-in/categories/142180/posts/460875

        Please let me know if it is still not working for you. Thanks!

        Let me know if that helps. Thanks!

        Reply
          simonrule - December 27, 2016

          it’s working now but there is a little problem is when i use paste the window closed but this note happening in the other options like right down…

          Reply
          Jon Acampora - December 27, 2016

          Hey Simon,
          Glad to hear it’s working now. This behavior of the window closing is a limitation of the Paste option. This option uses the SendKeys method in VBA to perform the paste. It’s basically the same thing as you press Ctrl+V on the keyboard to paste. The advantage is that the undo history is saved. The disadvantage is that the SendKeys method can be tricky to program. In order to get it to work I had to close the form and not take any more actions like selecting the next cell. It can cause errors and paste the value in the next cell. A lot is dependent on processes running on the users computer, and that is impossible to predict. So for now, the only way to perform the Paste is to have the form close.

          You can add the List Search button to the Quick Access Toolbar, then use a Alt+Number keyboard shortcut to re-open it. Here is an article on how to create keyboard shortcuts for the Quick Access Toolbar.

          I hope that helps.

          Reply
          simonrule - December 27, 2016

          it’s good idea thank you so much for the help 🙂

          Reply
Sam Sammito - November 22, 2016

Jon,
There are some really cool formula based solutions to this problem, but they require a lot of setup work for each validation list in your file. can you show us those formulas and setup requirements some day? If you ever do work on other PC’s knowing the long way sometimes pays off. I do want to thank you for your amazing relentless work and dedication to teaching excel better that most documentation does!

Reply
    Jon Acampora - December 1, 2016

    Thank you Sam! I agree that there are some really cool (and complex) formula based solutions. I will add these to my list for future articles. I try not to put too many complex formulas in my models because it makes it harder to pass on the maintenance to someone else, but it is great to learn the functions and techniques used within those formulas. Thanks again!

    Reply
Jim - November 10, 2016

Hey Jon,

When I try to use the button to copy the list of drop down options to the clipboard I get an error “memory location could not be unlocked. copy aborted”

Happens every time with any list. Any clues as to why that would be so?

Best regards

Jim

Reply
    Jon Acampora - December 1, 2016

    Thanks for reporting this Jim! It looks like you are probably using 64-bit Excel. I think I found the fix, although I have not been able to test because I don’t have a machine with 64-bit right now. I updated the add-in file in the downloads area. Can you download the updated file and let me know. There are updated instructions on the site as well. Thanks!

    Reply
Hervé Thiriez - November 9, 2016

Hello Jon,

I tried to download the Search add-in, but my e-mail is rejected and even trying “forgot your password” fails.

Best regards. Hervé.

Reply
    Jon Acampora - November 21, 2016

    Hi Herve,

    I’m sorry to not get back to you sooner. I just created an account for you, and you should receive an email with your username/password to the Excel Campus members site where you can download the add-in.

    Thanks again!

    Reply
Uwe - November 8, 2016

Hi Jon,

Thanks for the Add-In. This helps immense with large validation lists. In the past I always decided not to use validation lists if more than seven to ten entries would have been necessary. This might change now.

But I am afraid your macro crashes if Application.ReferenceStyle = xlR1C1.
You would need to convert the formula.
The following code helps if you insert right after the following line the code of the form:
If sFormula “” Then ‘Cell has validation list

Insert below:
If Application.ReferenceStyle = xlR1C1 Then
sFormula = Application.ConvertFormula( _
Formula:=sFormula, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
sFormula = Replace(sFormula, “[” & ActiveCell.Worksheet.Parent.Name & “]”, “”)
End If

Reply
    Jon Acampora - November 14, 2016

    Thanks for the fix Uwe! I rarely use R1C1, so that is good to know. I’m happy to hear you will be using validation lists more often with List Search. 🙂

    Reply
John Wilking - November 8, 2016

Will it also work on a MacBook Pro computer?

Looks like a great app.

Reply
    Jon Acampora - November 14, 2016

    Hi John,
    The add-in only works on the Windows versions of Excel. I don’t have a Mac version at this time.

    Reply
Mayank Jain - November 5, 2016

Great work Jon,
the passion u carry for ur work really motivates me.

Reply
Brewmaj - November 4, 2016

I’ve been trying to create something like this off and on for a while. Much appreciated.

I do have one issue I’d like to bring to your attention. I am using four monitors. When I have a spreadsheet on a monitor other than my ‘Main Display’ the form does not appear at the spreadsheet. Instead, it appears at the appropriate location, but on my Main Display. I think the Move_Form() controls this, but not sure if it can be modified to account for multiple displays.

I was also trying to modify the code to accept the TAB key to do the same as the down arrow. I added ‘And KeyCode 9’ to the list, but that didn’t do it. I’ll keep looking. it may not be possible.

Reply
    Jon Acampora - November 14, 2016

    Hi Brewmaj,

    The Move_Form macro does control the location that the userform appears. It is very basic code, and does not really account for multiple monitors. Chip Pearson has a Form Positioner macro that is much more advanced and will probably handle these scenarios much better.

    The TAB key can be tricky because it is also used to select the next control in the userform based on the tab order. I hope that helps.

    Reply
Mira Hassan - November 4, 2016

No bad comment. Only thank you so much. If I got it last week I will for sure do my test much better…. Great Job…..

Reply
Carrie - November 4, 2016

Looking at the preview, it sure does look awesome. Kudos! =)

I have to wonder about being able to search cell formatting though. I color code various things in my table in order to filter for them. (for ex I use different fill colors to denote different job types, and a common font color to tie the open work orders together. Everything else would be a normal/clear format. I would use bolds or italics too, but you can’t search for those… that I know of.)

For that matter, is it possible to do multiple filter/searches? Heehee, sorry, we ask for so much! =P

Reply
    Jon Acampora - November 21, 2016

    Hi Carrie,

    You can search for formatting using Excel’s built-in Find Window (Ctrl+F). Here is a screenshot of the Find Window. Press the Options button to display the Format.. button on the find window. From there you can set the formatting properties you are looking for by pressing the Format… button. You can also select the Choose Format from Cell… option from the drop-down to select a cell with the formatting properties you are looking for.

    Find Cell Formatting in Excel with Find Window Ctrl F

    I hope that helps answer your question. I will create a video on this in the future. Thanks!

    Reply
Ibo - November 3, 2016

Very Nice Work!

Just to help you improve this, I would change the function ShowSearch to below so that when there is no active worksheet, the code would not break:

If Not ActiveSheet Is Nothing Then
f_ListSearch.Show
Else
MsgBox “An active worksheet is required to use this feature.”, , “Active Worksheet Required”
End If

Reply
jomili - November 3, 2016

I have a question regarding “Paste”; if I understand correctly, if I choose “Down” or “Right”, the add-in places the value in the cell and then moves down one or right one. Am I right that this is a Paste operation, followed by a move down or to the right? Is it that second action, the move, that clears the Undo history, hence the reason for the “Paste” option? I guess I’m confused because it seems to me that at the user level all three options, Down, Right, and Paste, all paste the values in the cell, the only difference is where the cursor goes next.

Reply
    Jon Acampora - November 3, 2016

    Hey Jomili,

    Great question! Sorry if the explanation was confusing.

    The undo history is cleared when the add-in inputs the selected value into the cell. When VBA code makes changes to the worksheet like this, then the undo history is cleared.

    The undo history is not cleared when VBA code selects a cell. So the issue is the code that inputs the value.

    As a workaround, we can use code to copy the input value to the clipboard, then mimic the Ctrl+V keyboard shortcut to paste the value to the selected cell. This is what the “Paste” option does, and it uses the SendKeys method in VBA.

    You will also notice that the form closes after hitting the input button/Enter key, when using the “Paste” option. The selection does NOT change after the value is pasted. This is because the SendKeys method is somewhat unreliable. It works differently on different computers and setups. So to keep it as simple and reliable as possible, the code does not take any other actions after performing the paste.

    Therefore, I put the “Paste” option in the same drop-down menu as the directions. It would be great if we could paste the value, then move down or right one cell. I originally had it setup to do this, but my tests were not consistent. My Paste Buddy add-in does something similar with SendKeys, and I’ve experimented a lot with getting it to work. However, I’m definitely open to suggestions.

    If you add the List Search button to the QAT and use a keyboard shortcut to open it, then it should be pretty quick to perform a “Paste”, move down or right with the arrow keys, then re-open the form.

    I hope that helps answer your question. Please let me know if you have any other questions. Thanks!

    Reply
Derek - October 30, 2016

This is a fantastic add-in and thank you for making it free! It has already saved me lots of time searching my huge lists of data! Looking forward to learning more from your site. Keep up the great work!

Reply
    Jon Acampora - October 31, 2016

    Thank you Derek! I’m happy to hear you like it. I have a few updates coming out this week, and I’ll keep you posted on those.

    Reply
Mark - October 30, 2016

Is it Christmas already?! I can’t believe you’re giving such a useful tool away for free. Plus, I get to study your VBA code. Thank you for sharing this. I’m really looking forward to installing it on my office laptop tomorrow.

Reply
    Jon Acampora - November 1, 2016

    Thanks Mark! I feel like this is one feature that should be built into Excel, and that a lot of users can benefit from. I will be releasing an update this week with a few new features as well. I hope you enjoy it. 🙂

    Reply
Michael Fate - October 26, 2016

Hi Jon,

Love the add-in! Thank you very much. One thing I noticed is the setting for “Select Next Cell” doesn’t hold after I close and reopen the workbook. It defaults back to “Down”. I suppose I could add a variable somewhere in the code to hold the setting but I thought you might consider adding it to your add-in.

Thanks again for a great solution,
Michael

Reply
    Jon Acampora - October 27, 2016

    Thank you Michael! I will be releasing an update that fixes that problem, and has a few new features. Everyone that downloads the add-in will receive an email when the update is available. Thanks again for letting me know. 🙂

    Jon

    Reply
ayaz - October 24, 2016

great work sir

Reply
Tariq Shaikh - October 14, 2016

Hi Jon,

Thank you for the useful add-in and an excellent video that goes in real details and explains in non-technical terms. Appreciate it.

Reply
Barb Cardenuto - October 14, 2016

I really like your new addin. I also like the Tab Hound video link. Is that avaliable as a free addin?
Is there a way to copy the unique list of choices from the list search when it’s not part of a validation list? If I have 1000 rows of names and only 30 unique items in the list, I see your tool presents me with the unique names but now I’d like to just copy the list of 30 without having to copy the whole list and then remove duplicates to arrive at the unique list. Is this possible?

Thanks a bunch.
Barb Cardenuto

Reply
    Jon Acampora - October 14, 2016

    Hi Barb,

    That is a great suggestion! It does not currently do that, but can easily be added. I have a few more changes I want to make, and will publish an update to it with that copy list feature as well. It would be a lot faster than using the Remove Duplicates feature on the Data tab.

    Tab Hound is a paid add-in. Here is a link to the Tab Hound page that has more info. I also added a new Table of Contents Gallery feature to Tab Hound last week. It’s a great way to spice up your workbooks. 🙂

    Thanks again for the great suggestion! I will send out an email to everyone that has downloaded List Search when it is updated.

    Reply
james - October 14, 2016

Jon

thank you for this it looks great
been looking around the internet for ages trying to build something like this when I should have come straight here

thank you

James

Reply
    Jon Acampora - October 14, 2016

    Thank you James! I’ve been wanting this feature for along time too. The idea to create this solution popped into my head on Sunday, and the List Search add-in was born… 🙂

    Reply
Harvey - October 13, 2016

Hi.

I have been trying to do something like this for a very long time, so very many thanks.

Can you please tell me how to access it using my keyboard? I would prefer not to have to keep going to the “XL Campus tab” and clicking the “List Search”. For me the utopian would be to type a couple of letters in the cell, then press a “function key” or a “Ctrl-character” and it would then insert what I had typed into you form and find all the matches.

Even using the Quick Access toolbar would better – I know how to assign macros to buttons on there, but not VBA addins.

Even if there is no way to do what I have asked, I am sure I will use it a lot as I do use a lot of drop-down lists

cheers

Harvey

Reply
    Jon Acampora - October 13, 2016

    Hi Harvey,

    Great question! You can right-click the List Search button and select “Add to Quick Access Toolbar”. Then use the Alt+Number Key keyboard shortcuts to open List Search with a keyboard shortcut. Here is a video that explains how to do this with my Tab Hound Add-in. The process will be the same with List Search.

    Keyboard shortcuts can also be programmed into the add-in to open the form. I might add functionality for that someday. But the QAT is the quickest way to open with a keyboard shortcut right now. Let me know if you have any questions. Thanks!

    Reply
      Harvey - October 14, 2016

      Wow – I really was not expecting a reply so quickly – much appreciated. That worked perfectly, and I am pleased to see that I can also use F4 to close the form – so my mouse is not required – wonderful 🙂

      I now have a lot of icons in my quick access toolbar – I am using “MS Office Professional Plus 2016”. Is there any way that I can create my own icons, &/or include short descriptions alongside the icons in the Quick Access toolbar?

      Many thanks

      Harvey

      Reply
        Jon Acampora - October 14, 2016

        Hi Harvey,
        Thanks for the F4 tip on closing the form. That is going to be Alt+F4 for most users. The Escape button clears the text box, so that is why it doesn’t work to close the form.

        You can add your own macro buttons to the QAT, by choosing Macros from the Commands drop-down in the customization menu. You can then change the icon for the button and modify the display name by clicking the Modify button.

        I hope that helps. Thanks again!

        Reply
Manny - October 13, 2016

Hi John,

I love your blog and what’s impressive is your teaching methods, simple and precise.

The only complain is that I would like you to blog at least 3-4 times a week.

Cheers, Manny

Reply
    Jon Acampora - October 13, 2016

    LOL! Thank you Manny! I really appreciate your support. My fingers might fall off if I do that much typing… 😉

    Reply
Rick Langstaff - October 13, 2016

Hi Jon,
I’m eager to download List Search and give it a run. However, I’ve encountered a use I think your add-in addresses, but I can’t be sure from the description. I had a project a few months ago for a software company with an accounting and project management application used in the construction industry. The Excel complement I was working on pulled employee names from the application via ODBC and made them available in validation lists on an Excel form populated by field managers. The field managers usually didn’t have remote access to the home office’s database.

My validation dropdown didn’t have the google-like search capabilities of your add-in, so that’s an enhancement that would have been appreciated. But there was something else they wanted that I think List Search may allow.

The field managers often hired people at the job sites who weren’t in the company system. They therefore needed to be able to type names into cells where the form enforced validation. To accommodate that I wound up leaving a few rows on the form without validation, but even that was less than ideal. The Excel form had to mimic a paper form required by the state, and the number of rows was limited.

Toward the end of your write-up you say “List Search works on cells that do not contain data validation too.” If that’s the case, am I right in assuming I could leave cells (rows) on my form without validation so that field hires could be typed in and those same cells could still be used as if they’d been pre-set for validation only?

I really appreciate all your work, especially the training courses.

Thanks,
Rick

Reply
    Jon Acampora - October 13, 2016

    Hi Rick,

    Great question! Yes, the List Search Add-in could handle the issue of adding names to a cell that contains data validation, but does not contain the new name.

    When the value is entered in a cell through List Search, it is being input with VBA code. When inputting values into a cell that contains validation with VBA, the validation rules do not apply. That means you could input a value in a cell that contains validation and break the rule, and a warning message/error will not appear. I built some basic validation into List Search to handle this, but you could take that code out. The Input_Value macro in the f_ListSearch code module has a section labeled “Validate Entry”. You can just replace that code block with

    bExists = True

    This will prevent List Search from checking to see if the input value in the form exists in the data validation list.

    You might also want to add some code to add the new name (input value) to the validation list source table or range. That way the new name is automatically added to the list for future use to prevent typos. That code could be added to List Search with a macro that is called in the Input_Value macro.

    I hope that helps. I have developed headcount rosters on budget templates like this before, so I understand the process. There are definitely a lot of possibilities with this tool and I’m excited to hear everyone’s ideas. Thanks again!

    Reply
jaypogi - October 13, 2016

Thanks for this vieo xD
your such a great man who does great works!
Simply extravagant!
Thank you!

Reply
Shovan Lal Saha - October 13, 2016

Hi Jon, If in the sheet1, A1=Current Date, B1=Apple, guava, banana (from drop down menu), C1=Price, D1=Seller. When I shall input data in sheet1 (Only in B1, C1, D1) and those will be replaced but stored in Sheet2 (for Apple), Sheet3 (for guava), Sheet4 (for banana) according to date. Is it possible?

Reply
    Jon Acampora - October 14, 2016

    Hi Shovan,
    That is definitely possible with a macro. This add-in does not do that, but you could add VBA code to make that happen.

    Reply
      Shovan Lal Saha - October 14, 2016

      Hi Jon,
      Would you like to provide me a link that I can follow at this regard?

      Reply
Peter Raiff - October 13, 2016

Hi Jon,

I love the idea of this addin. The problem that I am having with it is that when the userform for the tool opens, it is not wide enough to get to the options such as the sort option or what to do when the enter key is pressed.

Reply
    Jon Acampora - October 13, 2016

    Thanks Pete! I forgot to mention that you have to press the Menu button in the form to view the additional options. Here is a screenshot.

    List Search Add-in Menu Button Options

    Does the form still not widen when you press that button? This could be a bug. Thanks!

    Reply

Leave a Reply: