Expand data validation to more than 8 entries

[Rock5150]

How do i expand the visual list of items for data validation dropdown? Its currently at 8 per 'screen' and want to make it to say up to 30-40. I know its long but i don't like using the pg down or scroll bar. I know i have seen using maybe forms control but i do like data validation because i can highlight the cells i want to use for the drop down and click the arrow drop one for each cell.

 

[Joe4]

You cannot do it with Data Validation, but if you use a Combo Box instead, you can show more than 8.
See: How to show/display more than 8 lines in data validation list in Excel?.

 

[Rock5150]

Thanks Joe4. Ok now that i have that, the only thing is how do i apply for say 100 other cells? In the cell reference i just put one cell for where the form control is at. HOw do i copy so that it will copy the same form control in cell reference to the next cell below? Can that be done? Or do i have to do a form control for 'each cell"?

 

[Joe4]

Hmm... not sure. Never have tried to do that before.
I am logging for a while in a few minutes, but found another link which may be helpful (though I have not had time to go through it thoroughly).
Have a look here: Show Excel Data Validation Drop Down Items in Combo Box

 

[Rock5150]

In short i don't want to create 100 "control" boxes. I want to maybe create one and copy however i'm finding that cell reference link is tied to use for an index. I don't know if that cell link reference in the control properties can be copied cell reference. I doubt it. So in cell AF2, have the drop down show say 30 names. I did use the controls form and it 'works' however again i don't want to create another form for AF3, AF4, etc.

Alexander Jeffrey
Alston James
Archuleta Michael
Atkinson Ray
Avery Al
Baker Bea
Battle Joseph
Battle Michael
Bell Lamar
Bethley Margie
Blanton Cheryl
Bolden Anthony
Briggs Shawn
Brister Antonio
Brown Art
Brown Darnell
Brown Kendall
Carter Leonard
Carter Myra
Colebrook James
Colebrook Sean
Condry Daryle
Cooper Kelly
Crawford Patricia
Davis Harold
Davis Murry
Daye Bobby
Dixon Lonnie
Duarte Dario
Dubose Damitoin
Eaton Bill
Edmonds Jojo
Edmonds Scott
Ely James
Fernandez Cy

 

[Akuini]

In short i don't want to create 100 "control" boxes.

If you're interested, here's an example of a searchable drop-down:

Macro to create searchable data validation+combobox

Akuini submitted a new Excel article: Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the...

www.mrexcel.com

OR

use an add-in called "Search deList"

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

I created an add-in called “Search deList”, and I 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 multiple keywords (separated by a...

www.mrexcel.com

 

[AlphaFrog]

Debra Dalgleish over at Contextures has a simple and clever bit of code that uses one Combobox that repositions itself on top of any data validation dropdown cell when clicked.

Data Validation Combo box Click​

 

[Rock5150]

AlphaFrog, thanks for the link. I got the combo box, properties set and copied the code but for some reason similar to the sample file, the "combo box" is not laying on top of the default data validation box similar to the sample file. I rechecked the code and i copied everything and put my list of data on the same sheet as the active worksheet. Any advise? If you need the cells exported here i can do that but not sure if those combo boxes will show up. Let me know please. Almost there

 

[Rock5150]

AlphaFrog, ignore. I was in "design mode" still for the combo box lol. THis works great!

 

[AlphaFrog]

Thanks for the update. Glad you got it working.