Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on 

Longer drop-down menu for Data Validation list in Excel

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Also, is there a way in Excel to auto-fill (ie so that as you begin typing, the only options displayed are those which match what you have typed so far)?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Yes there is.
Avatar of WeThotUWasAToad

ASKER

Thanks for the quick reply Martin.  So is a ComboBox a form of ActiveX?  I'm not familiar with either nor am I familiar with VBA.
SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of WeThotUWasAToad

ASKER

Thanks for the helpful responses.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo