How can I make a drop down box visible within a worksheet?

[Otto Moehrbach]

Sassy
Select the cell in which you want the DV.
Click on Data - Data Validation.
In the "Allow" area, select "List".
In the "Source" area type your data, for instance "a,b,c,d,e,f" without the
quotes. Your list would then be the letters a thru f.
If your list is more extensive and you don't want to type it all out, enter
your list items in sequential cells somewhere in your file. Name that list
something, say MyList. You name a list by selecting all the cells in the
list, click on Insert - Name - Define and type in MyList. Then in the
"Source" area of your DV setup type "=MyList" without the quotes. Click OK.
Done. HTH Otto

 

[Gord Dibben]

I thought you had already created DV dropdowns but didn't like them because the
arrows don't show up.

Peo was directing you to use a combo-box which would have visible arrows.

What do you exactly want now?

Don't ask me where the Office Button is..........I don't use 2007


Gord Dibben MS Excel MVP

 

[Cajun Laura]

I have created the combo box with the instructions below. I am trying to
link the choice to populate in a cell in another worksheet in the same file
but am having trouble making that happen. Any help would be appreciated.

Thansk,

 

[Kat]

Office button > Excel Options, make sure "Show Developer Tab" is selected.
Click the Developer tab > Controls > Insert Combobox from the active x
controls,
right click the drop down and select properties, in that pane that opens.
Put the range with your list in the
listfillrange and the cell you want to link to. Click the design mode button
to get out
of design mode


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

I am having the same problem Jenny had. When I get to the listfillrange, I
don't know what to put there, and everything I've tried doesn't work. HELP!
How can I get the arrows to stay?
Thanks!
Kat

 

[Cajun Laura]

I was able to make the box appear using this information, but the formulas in
the others worksheets that were linked to the cell containing the data
weren't able to reference the cell once it was done this way and not through
'Data validation'.

If there is a solution that anyone is aware of (or if I just missed
something), comments are appreciated.

Thanks,

 

[angela]

I have created several Drop Down boxes in a worksheet but I want to make them
visible so people know there is a drop down box there. Right now the arrow
doesn't show until you click on that cell. Please help I am not quite sure
how to format it so the drop down arrow stays.

 

[angela]

Hi Jenny,

Did you manage to create the drop down box that is visible? If yes, can you
give me a step by step idiot guide 'cos I have been struggling the past week
without success.

tq vm,
angela

 

[Corey]

Sounds like you have a Validation List and need a Control Combobox.
It will have a visible arrow always.
Do this:
1). Right Click on the top toolbar to Ensure Control ToolBox is ticked
2). 9th Icon on the toolbar is a Combobox
3). Click it and drag it in place over a cell you want it to be shown on.
Ensure 1st Icon has a Square around it(Desging Mode)
4). Double click the Combobox on the sheet to view the code
It should say:

Private Sub ComboBox1_Change()

End Sub


Click the Small drop arrow on the Right side, next to the CHANGE in the box,
and select DropButton_Click
Should then look like this:

Private Sub ComboBox1_DropButtonClick()

End Sub



5). Enter the Code to populate the cells data you want to load in it
eg.
Private Sub ComboBox1_DropButtonClick()
ComboBox1.Clear
ComboBox1.AddItem Sheet1.Range("A1")
ComboBox1.AddItem Sheet1.Range("A2")
ComboBox1.AddItem Sheet1.Range("A3")
ComboBox1.AddItem Sheet1.Range("A4")
ComboBox1.AddItem Sheet1.Range("A5")
End Sub

6). Uncheck the Design Icon(1st icon on Control Toolbar again)removing the
square.

Click the Combobox to see if the values show in the box.


Corey....

 

[Gord Dibben]

You cannot make the arrows visible on DV list dropdowns.

Either change to a ComboBox or just color the cell background.

You could insert a small triangle from the Drawing Toolbar in one side of
the cell as an alternative.

Set it to move and size with cell.


Gord Dibben MS Excel MVP

 

[PTexas]

I've followed the instructions (Thank so much Corey!) and created a Control
Combo box in a sample worksheet using the exact example given (i.e., Cells
A1:A5 have numbers 9, 10, 11, 12, 13 in them.) However, when I select any
of those values within the dropdowwn, it doesn't show anything and the cell
remains blank where the value I selected should be. Can someone please tell
me "specifically" what steps I need so that when I select an item, say "10"
from the control combo box drop down, the selected item s(10) shows in the
worksheet.
Thanks!

 

[IanC]

Hi PTexas

Select View > Toolbars > Control Toolbox to make the Control Toolbox
visible.
On the "Control Toolbox", select "Design Mode" (top left icon - blue
triangle etc.).
Right click on the combo box and select "Properties".
In the properties list, find "LinkedCell" and enter the cell you want the
data to appear in.

 

[Cameron]

This places the box in an arbitrary location. Is there a way to control
where the ddb is located?

 

[Cameron]

I just get an error when I try to click FILTER. I am using 2007.

 

[Gord Dibben]

Cameron

The method posted by Bonnie has nothing to do with Data Validation dropdowns
or Comboboxes.

Stick with Peo's suggestion.

Go to Debra Dalgleish's site for instructions on using a combobox in
conjunction with a DV list.

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP

 

[ramesh]

I have created several Drop Down boxes in a worksheet but I want to make them
visible so people know there is a drop down box there. Right now the arrow
doesn't show until you click on that cell. Please help I am not quite sure
how to format it so the drop down arrow stays.

 

[Ashish Mittal]

How can you do this is Excel 2003?

I am considering that the learner is asking as to how can he / she get a
drop down visible in an Excel sheet column.

MS Office 2003:

Launch MS Excel 2003
Go to View -> Toolbars -> Control Toolbox
A tool panel shall open displaying all the control options.
Toggle to Sheet2 and key in the entries in a column which you want in
DropDown.
(Say Names of the months from A1:A12)
Select A1 to A12 -> At Top Left corner where you can see A1 written -> Type
Months -> Press Enter Key.
Toggle back to Sheet1 and select 6th button (Combo Box) from Control Toolbox
Panel.
It will turn the mouse cursor in a hairline cross.
Draw a Drop Down Button of the length and width of your choice.
Right click on the created Drop Down Button and select Properties button.
Fill in ListFillRange as Months (This is the name you have given in Sheet2
for the A1:A12)
Close the properties dialog Box
Now in the in Control ToolBox, Press the very first button named "Exit Deign
Mode"
And there you go..... A drop Down with the name of the Months is ready for
you.
You may do experiments for Radio Buttons and other controls too.

 

[Parm]

Hi

interesting discussion and helpful. takin git one step further is it
posssible to auto complete items in a drop down list by for eample typing the
first few letters. THis wuld avoid haveing to use the mouse.

Any help greatly appreciated

 

[Gord Dibben]

DV dropdowns will not autocomplete.

If you use a combobox with DV per Debra's instructions from the site posted
below you can have autocomplete as well as the arrows showing at all times.

http://www.contextures.on.ca/xlDataVal10.html


Gord

 

[LP]

Worked for me!
Thanks

OMG is everyone kidding? all you have to do is click on the cell you want to
have the drop down box arrow appear then go to DATA on the menu toolbar and
click FILTER then Click AUTO FILTER thats it. also make sure u highlight the
information you want to appear in the drop down menu.

 

[green eyes]

this was VERY helpful!!! Please ignore further instruction below because
everything below is actually more confusing...and Bonnie, you're method is
elementary and doesn't address the question