Searchable Dropdown 2

0

i make Serchable Dropdown with active x control combobox when i used Name for list of range then show only 2 records display or i used manual range then shows all match criteria 

Answer
Discuss

Answers

0

I made the drop-down in Sheet1 of the attached workbook work. I defined a named range 'ListRange' as $D$2:$D$1000, and a second named range 'ListFill' like this.

=ADDRESS(ROW(ListRange),4,1,1,"Sheet1")&":"&ADDRESS(ROW(ListRange)+ROWS(ListRange)-COUNTBLANK(ListRange)-1,4)
Note that the "4" in the formula defines column 4 (= D). 'ListFill' adjusts to include only the used part of 'ListRange' and is assigned to the 'ListFillRange' property of the combo box.

Note that you can't use the combo box's Change event to drop down the list. Since your Cbx has an arrow, clicking on that arrow will open the list. If you want to automate that you might use the WorkSheet_Activate event, perhaps, or some other worksheet event, and probably configure the Cbx without the drop-down arrow which, by the way, triggers the 'DropButtonClick' event.

The fanciful behaviour of your original combo box was caused by the presence of the named ranges 'idname'. After I removed them everything become normal. I didn't find out if there is anything wrong with the formulas you used to define those ranges. Instead, I presume that the problem was that you declared 4 ranges all of the same name. You can declare ranges of identical name on different worksheets. That will lead to occasional confusion. But you can't use the same name again on Workbook level, and you can't use any name twice within the same scope.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login