Dropdown list with more than eight items

**johnlovesbeer** · 10-05-2009, 07:45 AM

Thanks DonkeyOte, that fixed the basic problem. Now I need to copy that combo box to another 2000 cells in the same column. Is this possible? I can't just grab and drag like a standard data validation dropdown box. I hope I don't have to paste each one individually.
Many thanks for the first advice!
Cheers,
JLB.

**romperstomper** · 10-05-2009, 07:58 AM

You would need to copy and paste them. I would also very strongly advise against it. What exactly is the issue with scrolling the validation dropdown list, if I may ask?

**DonkeyOte** · 10-05-2009, 07:59 AM

I'm afraid so... moreover you need to ensure each has the linked cell updated appropriately.

Perhaps you should think of an alternative approach ?

What about using a User Form such that when the user double clicks on a cell (where you want the list) a Form appears with the ComboBox from which they can make their choice and write back to the cell... this way you have only one combobox to manage.

See attached as a basic illustration of my thoughts...

**johnlovesbeer** · 10-05-2009, 08:20 AM

In reply to Romper, my dropdown needs 70 different items listed so it's a bit too much for the data validation option.

In reply to DonkeyOte (great name!) the user form looks like the perfect solution. Is there an easy way to create one without using VBA. I'm fairly new to this and VBA looks a little daunting!

Again many thanks,

JLB.

**DonkeyOte** · 10-05-2009, 08:23 AM

Just to reiterate, a validation list can hold 70 values it is simply restricted in so far as

a) it can only display 8 rows at any given time

b) it does not have auto complete feature

Re: User Form - VBA is a necessity I'm afraid.

**romperstomper** · 10-05-2009, 08:27 AM

It is a good name though...

**johnlovesbeer** · 10-05-2009, 08:27 AM

Sorry DonkeyOte, I'm explaining myself poorly. I need a dropdown list with 70 rows. Looks like it's VBA. I think I'll be posting more questions!

Thanks for all the help,

Cheers, JLB.