extended List box

  • Thread starter Thread starter Ken Williams
  • Start date Start date
K

Ken Williams

Hi,
I need to be able to choose from a List of numbers 30.00 through 32.00. Such
as 30.00,30.01,30.02......
I tried a List box but it only holds 40 selections. Is there such a thing as
an "extended" List box that will hold 200 digits? This is only for my
personal use so anything will do.
Thank you.
Ken Williams
 
Create a list with the numbers you want to select from on a worksheet.
Give this list a named range. I think it needs to be a workbook named
range, rather than a worksheet named range.
When you do the data validation, enter = LIST (or whatever you've named your
range).

Let me know if you need any more assistance.
 
Using a forms list box (in xl 2003), I wasn't able to replicate your problem
about the 40 selections restriction. I filled the 201 numbers: 30.00, 30.01,
.... 32.00 in say C1:C201. Then drew a list box, right-click to format
control & set the input range to: $C$1:$C$201, cell link: D1 (say). I was
able to get all 201 selections showing & working in the list box. Clicking a
selection in the list box produced the number in D1 corresponding to the
position of the selection in the input range. To extract the actual number
selected into a cell, I placed in say, E1:
=IF(D1="","",INDEX($C$1:$C$201,D1))
 
Thank you Max. I'll try this route.
Ken

Max said:
Using a forms list box (in xl 2003), I wasn't able to replicate your
problem about the 40 selections restriction. I filled the 201 numbers:
30.00, 30.01, ... 32.00 in say C1:C201. Then drew a list box, right-click
to format control & set the input range to: $C$1:$C$201, cell link: D1
(say). I was able to get all 201 selections showing & working in the list
box. Clicking a selection in the list box produced the number in D1
corresponding to the position of the selection in the input range. To
extract the actual number selected into a cell, I placed in say, E1:
=IF(D1="","",INDEX($C$1:$C$201,D1))
Click to expand...
 
Using a forms list box (in xl 2003), I wasn't able to replicate your
problem about the 40 selections restriction.
Click to expand...

Likewise for a control toolbox's list box,
I could get all 201 selections to show/work using the property settings:

LinkedCell: D1
ListFillRange: C1:C201

The value selected in the listbox would appear direct in the linked cell D1

---
 
Hi Max,
Guess I'm just not getting this. I am brand new to Excel. The only way I
know to try this is to Data/Valadition/Select LIST then fill the numbers
into the Source window. I am only permitted to enter 40 numbers into the
box. I can't figure out how to use the Forms to create a list box.
Thanks,
Ken
 
Ken,

It certainly sounds like you're trying to use data validation droplist, not
a "real" list box drawn from either the Forms toolbar or the Control
Toolbox, which I had earlier presumed was your subject.

Try Debra Dalgleish's good coverage on the steps for data validation (DV) at
her:
http://www.contextures.com/xlDataVal01.html

The DV technique is, as per Barb's response to you, to use a named range eg:
MyList housing all your 201 items, then to put it in the DV's Source:
=MyList (instead of keying in all the individual items into the Source box
itself). Try Debra's page, where she explains and illustrates the steps very
well. I'm pretty sure you'd be able get it up and going in a short time.
 
Back
Top