Limiting Combo Box List

M

Mike R.

I have a combo box set up that references a listfill range. Depending on a lookup, the range varies between 10 and 125 entries. So when the list is small, there are lots of blank spaces in the pull down. Is there a way to get the list to ignore blanks, as can be done with a list created by a validation?
 
E

Earl Kiosterud

Mike,

If there are no holes in your list (empty cells), define a name for your
list. Insert - Name - Define. In Refers To, type:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:Sheet1!$A$31),1)

This is for a list that starts in A2. Use that name in the listfill for the
combo box.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Mike R. said:
I have a combo box set up that references a listfill range. Depending on a
Click to expand...
lookup, the range varies between 10 and 125 entries. So when the list is
small, there are lots of blank spaces in the pull down. Is there a way to
get the list to ignore blanks, as can be done with a list created by a
validation?
 
M

Mike R

Thanks for the reply. What does the $A$31 in your formula refer to? Since the length of the list varies, this can not be the last list entry

----- Earl Kiosterud wrote: ----

Mike

If there are no holes in your list (empty cells), define a name for you
list. Insert - Name - Define. In Refers To, type

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:Sheet1!$A$31),1

This is for a list that starts in A2. Use that name in the listfill for th
combo box
--
Earl Kiosteru
mvpearl omitthisword at verizon period ne
------------------------------------------

Mike R. said:
I have a combo box set up that references a listfill range. Depending on
Click to expand...
lookup, the range varies between 10 and 125 entries. So when the list i
small, there are lots of blank spaces in the pull down. Is there a way t
get the list to ignore blanks, as can be done with a list created by
validation
 
M

Mike R

I assumed that the reference to $A$31 was for the last cell in the list. I tried this and only the first entry in the list is brought up in the combo box list. When you say "no holes" in my list, I assume you mean that the blank cells must be at the end of the list, which they are. Any suggestions

Mik

----- Earl Kiosterud wrote: ----

Mike

If there are no holes in your list (empty cells), define a name for you
list. Insert - Name - Define. In Refers To, type

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:Sheet1!$A$31),1

This is for a list that starts in A2. Use that name in the listfill for th
combo box
--
Earl Kiosteru
mvpearl omitthisword at verizon period ne
------------------------------------------

Mike R. said:
I have a combo box set up that references a listfill range. Depending on
Click to expand...
lookup, the range varies between 10 and 125 entries. So when the list i
small, there are lots of blank spaces in the pull down. Is there a way t
get the list to ignore blanks, as can be done with a list created by
validation
 
E

Earl Kiosterud

Mike,

A31 was the last cell in the largest list there might be. It could be
A65536.

Yes, "no holes" means no empty cells before the last non-empty cell.

I notice you say "depending upon a vlookup." Not sure what you mean.

If your combo box is ActiveX (from the Control Toolbox, instead of the Forms
toolbar), this won't work.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Mike R said:
I assumed that the reference to $A$31 was for the last cell in the list. I
Click to expand...
tried this and only the first entry in the list is brought up in the combo
box list. When you say "no holes" in my list, I assume you mean that the
blank cells must be at the end of the list, which they are. Any suggestions?
 
M

Mike R

Earl,
I did get it to work using the combo box from the control toolbar. However I have a bunch of these and it causes a lot of crashes. I tried using the combo box from the forms toolbar, as you sugessted that this would work, however it does not work at all. Shows all the blanks at the end of the list.

----- Earl Kiosterud wrote: -----

Mike,

A31 was the last cell in the largest list there might be. It could be
A65536.

Yes, "no holes" means no empty cells before the last non-empty cell.

I notice you say "depending upon a vlookup." Not sure what you mean.

If your combo box is ActiveX (from the Control Toolbox, instead of the Forms
toolbar), this won't work.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Mike R said:
I assumed that the reference to $A$31 was for the last cell in the list. I
Click to expand...
tried this and only the first entry in the list is brought up in the combo
box list. When you say "no holes" in my list, I assume you mean that the
blank cells must be at the end of the list, which they are. Any suggestions?
 
E

Earl Kiosterud

Mike,

Some possibilities for its including the empty cells: Recheck the
definition of the name (the offset function). Ensure that there's nothing
in the ostensibly empty cells. Try deleting them (delete key, not delete
command).

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Mike R said:
Earl,
I did get it to work using the combo box from the control toolbar. However
Click to expand...
I have a bunch of these and it causes a lot of crashes. I tried using the
combo box from the forms toolbar, as you sugessted that this would work,
however it does not work at all. Shows all the blanks at the end of the
list.
 
M

Mike R

Earl. Once again thanks for your help. Actually, I did not get it to work. What I did get however was for the pulldown to return to the top of the list each time it is selected. This will really do the trick, but I think now I have more function in there than I really need to simply get the pulldown to start at the top of the list. By the way, I can not delete the "empty" cells, since they contain a formula that returns either a value or "". Maybe that is the reason why I am getting so many crashes.. So my question is; is there a simpler way to get the control bar combo box to star the top entry in the list each time it is used, instead of starting at the previously selected value

----- Earl Kiosterud wrote: ----

Mike

Some possibilities for its including the empty cells: Recheck th
definition of the name (the offset function). Ensure that there's nothin
in the ostensibly empty cells. Try deleting them (delete key, not delet
command)

--
Earl Kiosteru
mvpearl omitthisword at verizon period ne
------------------------------------------

Mike R said:
Earl
I did get it to work using the combo box from the control toolbar. Howeve
Click to expand...
I have a bunch of these and it causes a lot of crashes. I tried using th
combo box from the forms toolbar, as you sugessted that this would work
however it does not work at all. Shows all the blanks at the end of th
list
 
Top