How to Read the Cell Value With Validation in Vba
Bottom line:Learn a fast and easy way to search whatever data validation listing or in-prison cell drop-down list with the new Excel feature orgratis tool.
Skill level: Beginner
New Feature to Search Dropdown Lists
In January of 2022, Microsoft released an update that lets you search dropdown (data validation) lists in the desktop version of Excel.
It'due south a feature I've been waiting on for a long fourth dimension, and I'm happy it's hither!
I originally wrote this post when the feature wasn't available, and information technology contains info on an add-in I developed called List Search, that allows you to search driblet down lists in cells.
If yous are Not on the latest version of Excel for Microsoft 365, and then the add together-in will nonetheless be a keen solution for you lot. It also contains some features that are not available yet in the native search feature in Excel.
Video Tutorial
How to Search Data Validation Lists
With the new update, you tin now blazon your search directly in the cell that contains the dropdown list. A list of results volition announced that lucifer your search term.
Yous tin select a result with your mouse or use the arrow keys and press Enter.
The search currently performs a "contains" type search for whole words. This ways the item y'all are searching for does Not need to start with the search term.
In the example below I am searching for apple. You can run into that the results include phrases like Fuji Apple tree and Granny Smith Apple, which contain the search term merely don't starting time with it.
A Few Bugs
As awesome as this new characteristic is, there are a few things I'd like to exist improved.
Enter Key Behavior
The first is the enter cardinal behavior. After you find an item and hit Enter to input information technology the cell, the same prison cell remains selected. You must printing Enter again to go to the next cell.
Now, this is Non technically a bug. The same behavior exists if you utilize Alt+Down Pointer to open the list and notice an particular with the arrow keys.
Yet, I recollect data entry would exist faster if the Enter key followed the same behavior it does when you enter data or a formula in a jail cell.
Fractional Discussion Matches
Another issue, and probably a bigger one, is that the search currently doesn't support partial discussion matches.
For example, my list contains the discussion Arrowroot. If I search "root", no results are returned.
Partial give-and-take matching does exist in other areas of Excel like the search box in the filter dropdown menus. So hopefully information technology will make it's manner to data validation lists also.
It volition exist very useful in a lot of scenarios, especially when your list contains part numbers, phone numbers, or account codes that all start with the same prefix and you lot want to search for text/number within the total value.
Availability
This new feature to search dropdown lists is currently available on the Beta Aqueduct of Excel, which is function of the Role Insiders programme. Office Insiders is free for all Microsoft 365 subscribers and you lot tin learn more nigh it here.
Notation that the feature is currently being flighted out, and then you might not have information technology notwithstanding even if you are on the Beta Channel.
It will roll out to the other Microsoft 365 channels in the coming weeks/months.
The search characteristic is also available on the web/online version of Excel. It was released for that version in 2021 and I covered it in my previous post and video on 21 New Excel Features Released in 2021.
What If I'm Stuck on an Erstwhile Version of Excel?
Older versions of Excel will not receive this new feature. Withal, I created a free add-in chosen List Search that allows you lot to search dropdown lists. I originally created this add-in in 2016, and it's since been downloaded over 30,000 times.
The add-in contains several boosted features for sorting, exporting lists, Enter key behavior, partial discussion matching, auto open, and more. So if you need that partial word match searching, you can utilize List Search on any version of Excel.
Video Overview of List Search
Click the links below to jump down to the characteristic update videos.
- November 2016 Update
- April 2017 Update
Search Validation Lists with List Search
The List Search Add together-in makes it fast and like shooting fish in a barrel to search whatsoever validation list. It also works with lists of data that practice not comprise data validation cells.
The Listing Search form contains a driblet-downwardly box that loads the selected cell'southward validation list. The driblet-down box also functions equally a search box. Y'all can type a search in the box and the results volition be narrowed down as you type. This is a Google-like search and the results will include whatever item that contains the search term. The item does not have to starting time with the search term.
Once you lot accept selected the item you are looking for, printing Enter on the keyboard or press the Input Value button on the form to input the value in the selected cell.
List Search works on any cell in whatsoever workbook. There is NO special setup required. Just select a cell, press the Listing Search push, and first searching the listing.
List Search Options & Features
The Listing Search Add-in contains some features that go far super fast to do data entry and piece of work with your lists. Printing the Menu button in the List Search window to see the options.
- Select Side by side Prison cell –Afterwards pressing the Enter key or Input Value push button, the prison cell below the agile prison cell is selected. This beliefs can be changed in the direction drib-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.
- Shut – closes the List Search window.
- Paste – Copies the input value to the clipboard and pastes it to the agile cell using the VBA SendKeys method. The List Search Window closes. This is the merely 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 gild by pressing the toggle buttons in the options menu. This only sorts the list in the List Search window. It does non sort the information validation listing in the cell.
- Listing Info – The Info button displays additional data virtually the drop-down list. It currently displays the full number of items in the list.
- Create List of Unique Values – A new push has been added that copies the contents of the drop-down list to the clipboard. You can and then paste the list to any range in the workbook. This is a fast manner to create a listing of unique values when you use List Search on a cell that does NOT contain data validation. You tin also filter the listing by typing a search, then copy the filtered list to the clipboard.
IMPORTANT Notation: When inputting values to the active cell, the only mode to retain the Disengage History is by using the Paste choice in the Select Adjacent Prison cell drop-down list. Listing Search uses macros to input the selected value, and macros typically clear the disengage history in Excel when they modify the workbook. The Paste selection is a workaround that uses the SendKeys method to re-create 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 prison cell that does Non contain data validation and open List Search, the drop-downwardly will be loaded with a listing of unique items from the cavalcade of the selected prison cell.
This is similar to pressing Alt+Down Arrow in a jail cell to see a list of values in that column. However, the list does not need to be contiguous. Fifty-fifty if the cavalcade contains blanks, List Search will yet load all the unique values in the current information region or listing.
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.
- Added a "Paste" selection to the directions list. This will re-create the input value to the clipboard and paste information technology to the activecell. The Paste options uses the SendKeys method in VBA to perform the paste. This means the Undo history will NOT exist cleared when using the Paste Option.
- Settings for the Options Carte and Input Direction Drop-down are now saved to the registry. Your preferences volition be saved and loaded when you open Excel and the add-in again in the hereafter.
- Added enhancements for Excel Tables. When the activecell is in a Table and the prison cell does not contain validation, a unique list of values will be loaded and exclude the Table headers and total row.
- Added Copy List characteristic that copies the contents of the drop-down list to the clipboard. This characteristic is used to create a list of unique values from a column/table when the activecell does non contain validation. Information technology besides works when the list is filtered with a search term to only copy filtered results.
Apr 2017 Update
Based on your crawly 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 Apr 2017 update.
- It added theMachine Open feature to automatically open the course when a cell that contains data validation is selected. You can toggle this option on/off with a toggle button in the options carte.
- The add-in now works with data validation created by formulas (Beginning & INDEX) and comma separated lists. It should work with all types of data validation lists.
- 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 course.
In the video I also showed some cells with driblet-downward button icons adjacent to them, fifty-fifty though the cell was not selected. Cheque out my article on how to make the validation list drop-down buttons e'er visible to learn more about this technique.
Download the List Search Add-in (information technology'south Costless!)
The List Search Add-in is complimentary to download and use. The VBA code is too open source so you lot can change it for your needs. This is likewise a not bad way to learn how macros and add-ins work if you are learning VBA.
Note: Yous will create a free account for the Excel Campus Members site to access the download and any futurity updates.
The download site also contains installation instructions and videos.
How Tin My Co-workers Apply List Search?
The List Search Add-in is installed on your estimator, and only you will be able to meet the 40 Campus tab and apply Listing Search. If you desire your co-workers to be able to utilise List Search there are ii ways to go about information technology.
- Send them a link to this page to download and install List Search on their calculator. They will be able to utilize List Search on any Excel file they have open on their computer.
- Import the List Search userform to the VB Projection in your Excel file. Y'all can add the List Search form to any of your workbooks. This must be a macro enabled workbook. You will besides need to create or import the code module that contains the macro to open the Listing Search userform. And then add together a push to the worksheet or ribbon that opens the grade. At that place is a video on the download site that walks through this entire process. In one case y'all opt-in to download the add-in you will receive a complimentary business relationship to access the Excel Campus Members Area and the download site.
The List Search add-in is as well available on our Hero Tools Add together-in.
The Hero Tools Add-in is packed with over 100 features that will save yous fourth dimension with your everyday Excel tasks. It will aid y'all automate processes with writing formulas, building pin tables, filtering data, table of contents, navigating workbooks, date picker, and then much more than.
Larn more about The Hero Tools Add together-in
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 observe the value we are looking for in long lists of data. Please leave a comment below with any questions or suggestions. Cheers! 🙂
Learn More most Dropdown Lists
Data validation lists are a slap-up mode to command the values that are input in a jail cell. These driblet-down lists also allow the states to choose options that can bulldoze financial models, reports, or dashboards.
- You tin can find my complete tutorial for setting upwards information validation lists here.
- Then you tin can learn how to make them dynamic here.
- And you lot tin can find out how to make them dependent on 1 another hither.
Notwithstanding, there is no born manner to search the validation list in Excel. It can be hard to roll through these lists when the drop-down contains a lot of items. There are some actually absurd formula based solutions to this trouble, but they require a lot of setup work for each validation list in your file.
Source: https://www.excelcampus.com/vba/search-data-validation-drop-down-lists/
0 Response to "How to Read the Cell Value With Validation in Vba"
Post a Comment