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

[skittles_golf]

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.
--
Jenny

[Peo Sjoblom]

You can't using data>validation, you can if you use a combobox from the
control toolbox

--
Regards,

Peo Sjoblom

"skittles_golf" <smil...@yahoo.com> wrote in message
news:91806D61-5E51-41E7...@microsoft.com...

[skittles_golf]

How do you create a combo box in Excel 2007
--
Jenny

[Peo Sjoblom]

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)

"skittles_golf" <smil...@yahoo.com> wrote in message

news:631A9566-9624-4C28...@microsoft.com...

[sparrot]

I am not sure what you mean by "Put the range with your list in the
> listfillrange and the cell you want to link to" I got to that part and am stumped.
Thanks

[Peo Sjoblom]

If the range is A2:A150 then type that in the listfillrange (click in the
box to the right of the word listfillrange
and type it there then close the properties window)

--

Regards,

Peo Sjoblom

"sparrot" <spa...@discussions.microsoft.com> wrote in message
news:DC0FE22F-3F83-467D...@microsoft.com...

[Shane Devenshire]

Hi Sparrot,

Why not add a comment to the appropriate cells and show the comment. Or you
could format the cells with a different color file or a border, then teach
your users what those formats mean. You could even conditional format the
cells to have a color until the user picks an entry from them.

Cheers,
Shane Devenshire

"sparrot" <spa...@discussions.microsoft.com> wrote in message
news:DC0FE22F-3F83-467D...@microsoft.com...

[Bonnie]

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.

[Peo Sjoblom]

You might want to check out the original post before you post something

The OP asked

"How do you create a combo box in Excel 2007"

it has nothing to do with autofilter

--

Regards,

Peo Sjoblom

"Bonnie" <Bon...@discussions.microsoft.com> wrote in message
news:0E634392-60DC-4256...@microsoft.com...

[Bonnie]

Yes i did and that wasn't the original post .. the original post was that she
wanted the drop down box to be visible .. not just when she clicked on the
cell she was on. The combo box was an answer to it then someone asked about
the combo box.

[Peo Sjoblom]

OK, explain to me how you would put a visible dropdown in cell A2, H10 and
I20
using autofilter? The OP said she put multiple dropdown boxes (using
data>validation obviously)
You cannot use autofilter for this

--

Regards,

Peo Sjoblom

"Bonnie" <Bon...@discussions.microsoft.com> wrote in message

news:A9EDF7AE-394B-4ADA...@microsoft.com...

[L]

How do I lock down the drop down list values so they don't change from the
control box when selecting an entry?

Your tip was spot on - big thxs

[Xavier]

Finally- someone who knows what the hell they are saying! BIG thanks from SD:)

[Kendra510]

How can you do this is Excel 2003?

[Gord Dibben]

View>Toolbars>Control Toolbox

Click on the "Design Mode" Icon and draw your Combobox.

Same instructions after that.

Gord Dibben MS Excel MVP

[Brainless_in_Boston]

Peo - you clearly know much more than me. Where is the "Office" button? I
certainly don't see it in my Excel icons...

MD
Boston, MA USA

[Dave Peterson]

That big circular button at the top left in xl2007 is the Office button.

--

Dave Peterson

[Sassy]

I'm using 2007 and it's not that easy for some crazy reason!

[Gord Dibben]

Why not stick with original thread so's people can see what has been suggested
and tried?

Gord

On Thu, 10 Jul 2008 13:19:03 -0700, Sassy <Sa...@discussions.microsoft.com>
wrote:

[Sassy]

I'm starting over from scratch....

So How do I get my info inside the DV drop down list? If anyone knows
please give me step by step instructions.

[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
"Sassy" <Sa...@discussions.microsoft.com> wrote in message
news:FA3D0237-C6CE-46B4...@microsoft.com...

[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

On Mon, 14 Jul 2008 09:26:01 -0700, Sassy <Sa...@discussions.microsoft.com>
wrote:

[Laura@discussions.microsoft.com 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]

"Peo Sjoblom" wrote:

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]

[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....

"angela" <ang...@discussions.microsoft.com> wrote in message
news:0BA3673C-DC7E-45E5...@microsoft.com...

[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!
--
PTexas

[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.

--
Ian
--

"PTexas" <PTe...@discussions.microsoft.com> wrote in message
news:02B7DF66-203B-46E0...@microsoft.com...

[Cameron]

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

[Cameron]

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

[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]

[Mittal@discussions.microsoft.com Ashish Mittal]

"Kendra510" wrote:

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

On Sat, 24 Oct 2009 04:12:01 -0700, Parm <Pa...@discussions.microsoft.com>
wrote:

[LP]

Worked for me!
Thanks

[eyes@discussions.microsoft.com 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

[and Beers@discussions.microsoft.com Cheers and Beers]

Here is how you would create a dropdown that is visible in the sheet (like a
form used for expense claims - don't ask teckies how to do real work!).

- Click on the Windows button (top left corner)
- Click on "Excel Options" button near the bottom of the drop down menu.
- Click on the third check box (default unselected) "Always show developer
tab in ribbon", then click Okay (Might as well leave it on, useful if your
doing this often Jenny)
- Click on the "Developer" tab, the click on "Insert", and under "FORM
CONTROLS" (Not Active X)
- Click and drag the box to the desired size and location.
- Right click on the box, and select "Format Control"
- In the new pop-up box, enter the cell range in the "Input range" column.
(Just like in 2003), then click ok.

Hope this helps; Sad it took a Canadian accountant to figure this out!

Cheers.

[ulhas....@techsoftsoln.com]

On Thursday, April 26, 2007 at 2:46:01 AM UTC+5:30, skittles_golf wrote:
> 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.
> --
> Jenny

It's working