Quickly Copy 4,000 Combo Boxes (all linked to different cells)

[WilliamPHII]

Hi! I have a column with over 4,000 data validation lists. All the lists are the same. The problem is, I want my lists to show all 35 items, instead of only 8, so I'm hoping to use combo boxes. Is there any easy way to fill in all 4,000 cells with the same combo box?

The other kicker is that each combo box needs to be linked to the cell it's resting on (or else to the cell immediately to its left or right). The reason of that is that I have another column that needs to read the info off the combo boxes (it's actually a second column of data validation lists that change depending on what the choice in the combo box is. The reason I'm fine using data validation lists in this second column is because those lists are shorter and so seeing only 8 items is fine.)

Maybe this is asking too much. Any help is appreciated.

Thanks.

William

 

[Fluff]

Can you explain what you mean by

I want my lists to show all 35 items, instead of only 8

Also putting 40 combos in the sheet will be a nightmare, along with the fact that the linked cell will not show the value selected in the combo, but the index number.

 

[Akuini]

I suggest using a combobox that can appear and hide automatically.
I created a combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type a keyword in the combobox and the results will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.
Here's a screenshot:

The workbook:
https://www.dropbox.com/s/w3anki62n...to cell, search as type - example 2.xlsm?dl=0

 

[WilliamPHII]

I suggest using a combobox that can appear and hide automatically.
I created a combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type a keyword in the combobox and the results will be narrowed down as you type.

Hold it! That's amazing! How do I do that?

 

[WilliamPHII]

What I mean is that only 8 items are visible at a time when using data validation. You have to do a decent amount of scrolling to see them all.

I agree about the nightmare part... that's why I'm on here!

 

[Akuini]

How do I do that?

Download the sample workbook, see the macro in sheet1 code module, there's a part that says:
'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================

so modify the code to suit.
Let me know if you need help.

 

[WilliamPHII]

Thanks so much! I will look into that, and will let you know if I need help. I'm currently trying to get my spreadsheet more finalized before I launch into the combo box thing, so could be quite some time before I get to it.