At a client's office last week, I was selecting a pricing option from a data validation drop down list. The worksheet was zoomed to 75%, so we could see more of the data, and the person watching over my shoulder asked, "How can you even read that?"
Good question. Sometimes the font in a data validation drop down is so small that you can barely read the list.
Unfortunately, there's no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list. There are other ways to create a drop down list though, and one of those options might work better in your worksheet.
We'll compare these types of Excel drop down lists:
- Data Validation List
- Form Control Combo Box
- ActiveX Control Combo Box
Data Validation List
Data validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. However, the font size can't be changed, only 8 rows are visible at a time, and only the active cell shows a drop down arrow.
You can't change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users.
Form Control Combo Box
Instead of using data validation, you could use a Form Control Combo Box. It gives you a bit more control over the appearance of the drop down list.
You can adjust the number of drop down lines in the Form Control Combo box, so you can show all 12 months, without a scroll bar.
The drop down arrow is always visible, to the Form Control combo box is easy for users to find on the worksheet. The font size can't be changed though, so the list would be hard to read on a zoomed worksheet.
If you select an item from the Form Control drop down list, the item number is entered in the linked cell. In the screen shot below, February was selected, and a 2 is entered in cell D5.
To show the item name, instead of the item number, you can use an INDEX function in another cell. In this example, the INDEX function returns a month name from the MonthList named range, from the row number (2) for the selected month (February).
ActiveX Combo Box
Another drop down list option is to use an ActiveX combo box. This is similar to the Forms Control combo box, but has more properties that you can adjust.
After you insert a combo box, right-click the combo box, and click Properties.
You can set the properties, such as font, font size, number of rows, and even include multiple columns.
If you know that a worksheet will be zoomed most of the time, you can use a larger font, to make the list easier to read.
Combine the Drop Down Lists
To make the most of the data validation features, while allowing font changes, autocomplete and more rows, I sometimes combine data validation with a combo box. There's only one combo box, hidden at the top of the worksheet, and bit of programming makes the combo box appear when needed.
When a user double-clicks on a data validation cell, the combo box appears, and displays the data validation list for the selected cell. If users don't want to use the combo box, they can simply click on the arrow in the cell, and use the data validation list.
Watch the Drop Down Lists Video
To see a demonstration of the 3 types of drop down lists, you can watch this Drop Down Lists video.
____________
You forgot there is one more type of dropdown list
Just type a list starting from A2 to say A10
Hide the rows.
Select A1 and type Alt+Down arrow OR rightclick button +k
Hi Debra,
As it happens, I have published an article about worksheet controls quite recently:
http://www.jkp-ads.com/articles/controls.asp
I don't have a comment. I'm just looking for: "Notify me of followup comments via e-mail". Won't let me leave a blank comment, so now I'm explaining. Let's see what works.
Thanks
Jeff
While I wouldn't recommend it, one option for Validation is to magnify the worksheet when the user clicks on the cell containing the validation. Something like:
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) "A1" Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 200
End If
End Sub
LOL I've got a post scheduled about the various ActiveX controls. I'll send a trackback when it's published.
@sam, thanks for the reminder about the Pick From List feature.
@Jan Karel and @JP -- obviously, great minds think alike!
@Jason, thanks for the code sample. That's similar to the example that I have on the Contextures site, and isn't a perfect solution, but can help in some situations:
Make the Dropdown List Appear Larger
Hi Debra,
I feel that many a times the width of dropdown box is similar to that of the column which contains the validated cell, but many a times it is much wider than that. Is there any specific reason for that ?
Thanks
Hi Kanwaljit, I haven't found a reliable fix for the problem, but there are a couple of suggestions on my Contextures website:
Data Validation List Too Wide
Hello,
Does anyone have an idea of using dependent drop down list from another workbook?
I could only get a list but not dependent list.
Note: some people may say that why don't you use dependent list in one excel sheet... As per the company policy, changing data means we are going to have long time to get it approved and though we have to change our document revision number everytime that data gets increased. that's why we need it in different workbook.
Thanks.
Redha, instead of a dependent list based on another workbook, you could use Microsoft Query to connect to the list in the master workbook. Set the connection to automatically update when the file is opened, and users will always have the latest version of the list from the master file.
Thanks Debra.
I've used VLookup and it works fine and no need to open both of the workbooks... but I'll also try microsoft query as well although never used it before on excel sheets!
Hello all,
maybe you can help me, I have a question related to this discussion. If I use form control, is it possible to create dependent list?
I know how to create dependent list with Data Validation (using INDIRECT reference), but it doesn't work when I use a form control. I want to use a form control, because I also need to get the value of my selection in a linked cell.
For example, I have a dropdown list with all car brands, depending on the brand that I selected, I can choose the correspondent models to that brand in a second dropdown list. And if for example, I select the third model in the list I want to see "3? in a cell.
thank you very much in advance!!
Need a little help, new to excel but with its flexability I need it...all I want to do (famous last words) is create a drop down box to show items in col. 1(I know how to do a drop down box) and when selected I want to show items I have in col. B Col. C and Col D.
Example;
A B C D
code error meaning countermeasure
When a selection from the drop down box (A) is selected, the corresponding data in col. B through D will be displayed...
Can you help me..
Norm
Norm, you could use a VLOOKUP formula in the adjacent cells, similar to this Excel order form
We have a client data base that has about 1000 entries and it takes a while to scroll through the list each time. I am trying to update where the user can start to type the customer id and it pulls up possible selections based on the first couple of letters or numbers entered. Is this possible?
Using the activeX control, can I make it work like option 2 – choose an item off the list, and make a number appear in the linked cell? I need the activex because I need to the font bigger, but want a corresponding number in the neighboring cell, not the words.
Hi All
I was wondering if someone would kindly help me. I am creating a Userform a need to create a dependent list in a combobox i.e. When a category is selected only items that relate to that category are displayed in the list of choices. I can create dependant lists using data validation in a worksheet but cannot translate this to a Userform. Is it even possible to do this?
Any help provided would be greatly appreciated.
Thanks
I have question on the Data Validation with a Combo Box. I created it in my spreadsheet (thanks for the instructions!) but am running into issues. Users can type their own entry, and there is no error message after they move to the next cell. How can I get an error message to force all entries to be from the list?
I feel like a dummy after reading some of the great questions above, but I'm going to ask anyway. I have been trying to create a drop down list in my spreadsheet. I have followed all the right steps, checked the "In-cell dropdown" box, yet it will never show the dropdown. It will not let me enter invalid values (ones that aren't aren't on the list I specified); it gives me an error message for that. But I cannot see the list from which I am choosing. I have been wrestling with this for hours. Any ideas? I am using Excel 2003.
@Bill, thanks for your question, and there are some solutions to the missing drop down arrow problem on this web page: http://www.contextures.com/xlDataVal08.html#ArrowsNotVisible
Thank you! The Hidden Objects option fixed it. I am going to bookmark this site!
Best,
Bill
Is there any way to include a drop down list in a formula. In my example I want to return a specific value if the value in an adjacent cell is no ut if the answer is yes I want it to return a drop down list. Any ideas?