We help IT Professionals succeed at work.
Get Started

Longer drop-down menu for Data Validation list in Excel

2,087 Views
Last Modified: 2017-04-23
Hello,

Is there a way to get a longer (taller) drop-down options menu when using a Data Validation list in Excel?

I have a spreadsheet in which one of the column headings is "Event Type".  In a different spreadsheet (but the same workbook) I defined a range of cells with the name "EventTypes".  I then applied Data Validation to the first column by selecting List (Settings > Validation criteria > Allow) and "=EventTypes" as the Source.

The EventTypes range currently has almost 20 entries but when the drop-down menu is opened, only eight of the options are displayed and vertical scrolling is required to see the remainder.

Is there an Excel setting which will enable the drop-down menu to be long enough to display all options and eliminate the scrollbar?  If not, is it doable using VBA or another method?

By the way, I found a similar question here: Way to incorporate more than Eight Items in Data Validation drop down in which the accepted solution says the drop-down menu length cannot be increased but that an alternative is to use a VBA ComboBox.

Is that reply (re menu length) correct?  And if so, how do you create a VBA ComboBox?

Thanks
Comment
Watch Question
Keep everyone healthy; Wear a mask - Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 3 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE