Creating a ComboBox

  • Thread starter Thread starter jdlucas
  • Start date Start date
J

jdlucas

I am new to using ComboBox's and need instructions n how to create them
I am creating a spreadsheet that requires a comboBox where you ca
select either "yes" or "no". Can you help me out
 
View > Toolbars > Forms

For a Yes/No option you would be better using a Check Box

Click on Check Box from the Forms Toolbar

Drag onto worksheet

Right-click on Check Box and select Format Control

Click in the Cell-Link box and then click on any cell in
the worksheet

Click OK

When the check box is selected (Yes) the value in the link
cell is True

If the check box is not selected (No) the value in the
link cell is False
 
Great thanks I will try this. But can you also tell me how to create
combo box? I know how to create the box itself but I am unsure of ho
to add the list on items that they can choose from.

:confused
 
There are dropdowns (comboboxes from the Forms toolbar) and comboboxes (from the
Control toolbox toolbar).

In either case, show the appropriate toolbar.
Click on the icon (both say "combo box")

When you move the cursor off the toolbar and on to the worksheet, the cursor
will look like a + (plus).

Click where you want to locate that combobox and then drag to the opposite
corner.

Now here comes the difference.

If you chose the Forms version:
right click on it and choose Format Control
on the Control Tab, you can specify both the "cell Link" and the "Input range".

This cell link will return an index into that input range.

You can get the text in that dropdown (combobox from the Forms toolbar) by using
a helper cell.

Say my input range is A1:A10 (more than you need in your case).
My linked cell is B1.
then in C1, I could use this formula:

=index(a1:a10,b1)
or maybe better:
=if(b1="","",index(a1:a10,b1))


If you chose the Controltoolbox version:
Use the design mode icon on that same toolbar.
rightclick on your combobox
select properties
Look for linkedCell (for the linked cell <bg>)
look for Listfillrange (for the "input range" for this version)

There's no need for a helper cell in this case. That cell will have the value
of what's in the combobox.

=======
Just some hints when you're positionining the combobox/dropdown:

Use the Alt key to snap-to a cell border. Use the Shift key to keep it in the
same vertical/horizontal plane.

You can find some more manipulation tools on the Drawing toolbar (click on the
Draw icon and you'll find Nudge, snap, align, and more).

=======

And why the different objects for stuff that looks almost the same. The
dropdowns came first. The comboboxes were added in xl97. So they hang around
for compatibility with old workbooks.

But I find the dropdowns (from the Forms toolbar) much easier to work with. But
they have a lot fewer customization capabilities--both for formatting and macro
use.
 
When you use the forms combo box & select (say) item 3 in the list it puts
the number 3 in the linked cell.

When you use the Control toolbox version it puts the text of item 3 in the
list in the linked cell - how do I make the Control toolbox version act like
the forms version & put just the number in the cell? I can get over it by
using Match() but mostly I use the combobox to determine which row of data I
want to pick up so the number of the item chosen is more important than its
description, but I want the editing freedom that the Controltoolbox version
gives me and avoid having match() functions every time.

Thanks in advance.
 
I think you'd going to have to use another helper cell and use your =match()
function.

Or you could try this:

Set up your range of values in two columns:

1 aaaa
2 bbbb
3 cccc
4 dddd
5 eeee
6 ffff


The in the properties for the combobox:
..boundcolumn = 1
..columncount = 2
..columnwidth = 0;99 (something wide enough to show your data)
..linkedcell = c1 (or whatever)
..listfillrange = A1:B6 (or whatever, again!)


Try it out and look at the linkedcell's value.

(I like =match() more and more!)



When you use the forms combo box & select (say) item 3 in the list it puts
the number 3 in the linked cell.

When you use the Control toolbox version it puts the text of item 3 in the
list in the linked cell - how do I make the Control toolbox version act like
the forms version & put just the number in the cell? I can get over it by
using Match() but mostly I use the combobox to determine which row of data I
want to pick up so the number of the item chosen is more important than its
description, but I want the editing freedom that the Controltoolbox version
gives me and avoid having match() functions every time.

Thanks in advance.
Click to expand...
 
Back
Top