Excel: How do I create a drop-down menu? (that includes the arrow)?
Posted by admin
I know how to create a drop-down list, but what I need is for the "drop-down arrow" to always be visible.
I have followed the instructions in MS Excel 2003 to a T, and it seems like they don't have that option!!
When I created the drop-down menu, I have to click on the cell before the arrow shows up (see pics)
If somebody is able to specifically tell me how to create the other version, I will send you $5 via Paypal — if you have a Paypal account. (but only if your solution works!)
Please log into my email account, and you will be able to see pictures of the screen shots (that way you will know what I am talking about)
The log-in is: ExcelDropDownHelp
The PW is: microsoft
Thanks for any help you can provide!!!
Jung
October 4th, 2009 at 3:41 pm
It sounds like you are using the Data Validation drop-down list. That only becomes active when you click the cell.
If you want the drop-down to be visible at all times, use the control from the Forms toolbar instead. Go to View –> Toolbars and click on Forms. On that toolbar, you will find a combo box (drop-down list) control. Click that and then click and drag on your worksheet where you want it to appear.
To use the control, you will need to have a list in your worksheet that contains the items to be shown in the combo box. Once you have that list, right click the control and choose Format Control. Specify the location of your list and where you want it to report the number of the item that was chosen.
When you choose an item from the list, the control will set a value in the Cell Link cell. You can reference that number to figure out which item was selected, and then your formula can respond appropriately.
Tim
October 7th, 2009 at 2:55 pm
just select your list and then go Data > filter > Auto Filter.
Is that wat you needed?
October 9th, 2009 at 8:06 pm
that behavior is not alterable.
the only thing I could think of is to find a clipart of a black down arrow and place it in the cell. Then, once you have it in the right place, right-click on the image and turn it into a hyperlink pointing at that cell.
Of course, this isn't great because there will still be a second black arrow showing up when user brings focus to that cell.
And, look for a way to expand the dropdown list when the user clicks on the image.
October 12th, 2009 at 9:18 am
You can also get a combo box from the control toolbox. you get an active X control. Double click in it and you go directly to visual basic and can add code to do what you want it to do.
October 13th, 2009 at 8:59 am
Tim and wvl have the right ideas. The combo box from the Forms toolbar is easier to use. But it is also an older version and has less options. Also, you'll probably have a more likely chance of that combo box being no longer able to be used in newer versions of Excel. I'm not sure if it is available in 2007, it might be. For it though, you just right-click the control, then format the control. Then you go the Control tab and enter your input range for the list to be displayed in the combo box. Then select the linked cell range for the cell that is going to change based on what you select.
The Combo Box from the Control Toolbox toolbar is much more up-to-date and has a lot more options. But adding the list is slightly more complicated. Also, a combo box from the Control Toolbox tool bar works much better with macros.
The simplest way to enter the list for a combo box from the control toolbox is to right click the combo box right after you create the combo box. Then click Properties. Then look for ListFillRange. Enter the range in that propert section. So if you want to grab the list from A1:A4 on the current worksheet type A1:A4. No quotes or anything have to go around range. You reference the cells just like you would in Excel formula, but without the equals sign. So to reference cells on another worksheet, say Sheet2, you'd do it like so:
Sheet2!A1:A4
If there is a space in the worksheet's name, you need single quotes around the worksheet name. So if it was Sheet 2 with a space between Sheet and 2, you'd need:
'Sheet 2'!A1:A4
Then go to the LinkedCell property. That is the cell that will change based on what you pick from the dropdown list. So it is similar to the Forms one.
After you are done, close the properties window. Then click the button on the Control Toolbox toolbar that looks like a drafting triangle. That button takes the objects you create in and out of Edit mode. So you are exiting the edit mode to finish.
As you might notice, you can change the size of the font, the type of font, the alignment of the text, etc with the Control Toolbox's combobox. You can't do all of that with the one from the Form toolbar.
I personally recommend going for the one from the Control Toolbox menu. It may be a little more complicated, but it is much better.