Forum Discussion
VBA Dropdown, not working on older versions of excel
BlakeBessigner wrote:Mainly looking for What version of Excel is causing this issue and how to work around it. The VBA code pastes all the devices I need to a column on the "Dropdowns" sheet, then it uses the Sort, Unique, and Filter functions to create the array of devices the dropdowns point to. When I run this on my machine it works 100% of the time, but when this code is ran on my Boss's machine it fails with an error.
Try using "Search_deList_v2.1", it's a free Excel add-in, it works on Excel 2007 or later.
Its function is to speed up the search in the data validation list. In any cell that has data validation (with type List) pressing ALT+RIGHT arrow will open a User Form with a combobox. You can type multiple keywords (separated by a space) in the combobox to search items on the list. It also gives you a unique & sorted list.
By using this add-in, you don't need VBA to have this searchable combobox, so you can save your files as .xlsx.
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.
You can find it here:
https://www.mrexcel.com/board/threads/i-created-an-excel-add-in-called-search-delist-to-create-searchable-data-validation.1189466/
- This is a small part of a VBA program that is used to create the initial code and CAD files for Projects at the company I work for. Searching and creating the dropdowns manually via an add-in would add further steps for the user and overcomplicate a process that is intended to be as easy as possible,
Additionally the dropdown's information is somewhat dynamic, being built earlier in the code. As shown in the code I provided in the post "ModuleDrop" is the array of the variables needed for the dropdown. Then the "=SORT(UNIQUE(FILTER(XXXX)))" function is used to sort and create the list of items the dropdown refers to.
I'm trying to find what within this VBA process is causing issues in older versions of Excel? Then how to work around this issue while still using VBASearching and creating the dropdowns manually via an add-in would add further steps for the user and overcomplicate a process that is intended to be as easy as possible,
The title is somewhat misleading. In reality, the add-in is not for creating data validation; rather, it is designed to automatically generate a searchable combobox based on existing data validation. The list in the combobox is sorted and unique.
