Number of entries in drop down box
 

I have a drop down box in my spreadsheet that I use for listing the
builders that I work with/for. It appears that the max. number of
entries is 12. Can I increase that?


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

Is it a validation dropdown or a worksheet dropdown or a combobox?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

I had to check. I believe it is a combo box!


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

If it's a combobox from the control toolbox toolbar, you can go into design mode
(another icon on that control toolbox toolbar), then click the properties icon.

Look for ListRows and change it to what you want.


If it's a dropdown from the Forms toolbar:
Rightclick on it and choose Format Control.
On the Control tab, change the drop down lines to what you want.


If it's a data|validation dropdown, you're stuck with what excel gives you.


theslaz wrote:

I had to check. I believe it is a combo box!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson

Number of entries in drop down box
 

Don't know what to say. I tried to check and see what properties the
drop down list had and really nothing shows. Yet when I created a "Test"
Combo Box and checked it's properties; it was listed as Combo Box 1 and
showed all it's settings including the "ListFiles" you mentioned.

At a loss as to what to try next in order to determine what in fact
this "Combo Box" of mine is!


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

ListRows, not ListFiles.

How many items are in the list?
What did you use in the .listrows property?


theslaz wrote:

Don't know what to say. I tried to check and see what properties the
drop down list had and really nothing shows. Yet when I created a "Test"
Combo Box and checked it's properties; it was listed as Combo Box 1 and
showed all it's settings including the "ListFiles" you mentioned.

At a loss as to what to try next in order to determine what in fact
this "Combo Box" of mine is!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson

Number of entries in drop down box
 

The properties box looks like this and has a line: List Rows 8 change
that:
28


+-------------------------------------------------------------------+
|Filename: combobox properties.jpg |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=28|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

Sorry; I meant Listrows; and yes I found the properties box and the
entry for Listrows which had the number 8. That was when I did the
"Test" combo box.

Right now I have 17 enteries in the "Builderlist" of which only 12
show. The combo box that I have references to this builderlist; however;
as I said; I can not determine how it does that; as when I click on my
combo box and then look at it's properties in Design mode; all that
shows is the properties for sheet 1 which is named "Invoice Data Entry"


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

When you're in design mode, select the combobox first. Then click the
Properties icon.

Then change the .listrows property to something else (17???).

theslaz wrote:

Sorry; I meant Listrows; and yes I found the properties box and the
entry for Listrows which had the number 8. That was when I did the
"Test" combo box.

Right now I have 17 enteries in the "Builderlist" of which only 12
show. The combo box that I have references to this builderlist; however;
as I said; I can not determine how it does that; as when I click on my
combo box and then look at it's properties in Design mode; all that
shows is the properties for sheet 1 which is named "Invoice Data Entry"

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson

Number of entries in drop down box
 

I've determined that it is not a Combo Box from the Control Toolbox tool
bar. It is a dropdown from the forms toolbar.

I have changed the number of lines it will display. It appears to be
working!

Thanks for your trouble and I'm sorry about confusion.


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

Glad we could be of help!

If your query has been solved please take a moment to let us and
eveyone else know by going to Thread ToolsMark Thread Solved,shown by
this icon 29


+-------------------------------------------------------------------+
|Filename: marksolved.gif |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=29|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

Simon Lloyd wrote:
Glad we could be of help!

If your query has been solved please take a moment to let us and
eveyone else know by going to Thread ToolsMark Thread Solved,shown by
this icon 29


+-------------------------------------------------------------------+
|Filename: marksolved.gif |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=29|
+-------------------------------------------------------------------+

Just thought I would let you know. I couldn't get the Combo box to work
properly (wouldn't copy to any other cells ). I therefore came across
avideo on YouTube that showed how to make a Data\Validation\Drop down
box. My brain fart than cleared and I remembered that was what my drop
down box was. I than checked to see what my range of cells was and I
just increased the range from 12 cells to 17 and it works fine.

Only think I can't understand is that the range that it references to is
a blank range of cells (=#A$230:$A$242). However; when I make changes in
a defined range (Builderslist)(='reference Tables'!$A$21:$D$37); which
is in a complete different area of the spreadsheet; these changes are
reflected in the drop down list.

Comment?

[SOLVED]: Number of entries in drop down box
 

Thats because you haven't created a Dynamic range name like this:

*=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
*
Your named ranges should be dynamic this way they expand and contract
with the data in the range


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

Number of entries in drop down box
 

Dave,

My question is in relation to this older posting about expanding dropdown
lists to include all choices, not just 8-12.

I'm currently using your 'myNavigator' code to create a floating toolbar
that opens each time I start Excel. The toolbar has a dropdown in it with
the names of all worksheets in the open workbook. How do I expand the
dropdown list so that all 60-70 worksheet names appear WITHOUT needing to
scroll?
Thanks.
Tondos

"Dave Peterson" wrote:

If it's a combobox from the control toolbox toolbar, you can go into design mode
(another icon on that control toolbox toolbar), then click the properties icon.

Look for ListRows and change it to what you want.


If it's a dropdown from the Forms toolbar:
Rightclick on it and choose Format Control.
On the Control tab, change the drop down lines to what you want.


If it's a data|validation dropdown, you're stuck with what excel gives you.


theslaz wrote:

I had to check. I believe it is a combo box!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson

Number of entries in drop down box
 

I'm not sure you can.

If you add a combobox to a userform, it has a .listrows property that you can
specify that number of rows to show. I don't see that property in the combobox
placed on a commandbar.

But maybe someone will see a way to do this...

Tondos wrote:

Dave,

My question is in relation to this older posting about expanding dropdown
lists to include all choices, not just 8-12.

I'm currently using your 'myNavigator' code to create a floating toolbar
that opens each time I start Excel. The toolbar has a dropdown in it with
the names of all worksheets in the open workbook. How do I expand the
dropdown list so that all 60-70 worksheet names appear WITHOUT needing to
scroll?
Thanks.
Tondos

"Dave Peterson" wrote:

If it's a combobox from the control toolbox toolbar, you can go into design mode
(another icon on that control toolbox toolbar), then click the properties icon.

Look for ListRows and change it to what you want.


If it's a dropdown from the Forms toolbar:
Rightclick on it and choose Format Control.
On the Control tab, change the drop down lines to what you want.


If it's a data|validation dropdown, you're stuck with what excel gives you.


theslaz wrote:

I had to check. I believe it is a combo box!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson


--

Dave Peterson

Number of entries in drop down box
 

An alternative to Dave's sheet navigator is code from Bob Phillips.

See this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw

You should easily be able to fit 60-70 sheets onto the Form.


Gord Dibben MS Excel MVP

On Sat, 14 Feb 2009 12:37:02 -0800, Tondos
wrote:

Dave,

My question is in relation to this older posting about expanding dropdown
lists to include all choices, not just 8-12.

I'm currently using your 'myNavigator' code to create a floating toolbar
that opens each time I start Excel. The toolbar has a dropdown in it with
the names of all worksheets in the open workbook. How do I expand the
dropdown list so that all 60-70 worksheet names appear WITHOUT needing to
scroll?
Thanks.
Tondos

"Dave Peterson" wrote:

If it's a combobox from the control toolbox toolbar, you can go into design mode
(another icon on that control toolbox toolbar), then click the properties icon.

Look for ListRows and change it to what you want.


If it's a dropdown from the Forms toolbar:
Rightclick on it and choose Format Control.
On the Control tab, change the drop down lines to what you want.


If it's a data|validation dropdown, you're stuck with what excel gives you.


theslaz wrote:

I had to check. I believe it is a combo box!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson

Number of entries in drop down box
 

Thanks, guys! I like Bob's macro, too (it appears to solve the problem!)
They're both awesome!
I really appreciate your help!
Tondos

"Gord Dibben" wrote:

An alternative to Dave's sheet navigator is code from Bob Phillips.

See this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw

You should easily be able to fit 60-70 sheets onto the Form.


Gord Dibben MS Excel MVP

On Sat, 14 Feb 2009 12:37:02 -0800, Tondos
wrote:

Dave,

My question is in relation to this older posting about expanding dropdown
lists to include all choices, not just 8-12.

I'm currently using your 'myNavigator' code to create a floating toolbar
that opens each time I start Excel. The toolbar has a dropdown in it with
the names of all worksheets in the open workbook. How do I expand the
dropdown list so that all 60-70 worksheet names appear WITHOUT needing to
scroll?
Thanks.
Tondos

"Dave Peterson" wrote:

If it's a combobox from the control toolbox toolbar, you can go into design mode
(another icon on that control toolbox toolbar), then click the properties icon.

Look for ListRows and change it to what you want.


If it's a dropdown from the Forms toolbar:
Rightclick on it and choose Format Control.
On the Control tab, change the drop down lines to what you want.


If it's a data|validation dropdown, you're stuck with what excel gives you.


theslaz wrote:

I had to check. I believe it is a combo box!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson

Number of entries in drop down box
 

Thanks for the feedback.

I too like Bob's sheet browser/selector.


Gord

On Mon, 16 Feb 2009 10:30:01 -0800, Tondos
wrote:

Thanks, guys! I like Bob's macro, too (it appears to solve the problem!)
They're both awesome!
I really appreciate your help!
Tondos

"Gord Dibben" wrote:

An alternative to Dave's sheet navigator is code from Bob Phillips.

See this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw

You should easily be able to fit 60-70 sheets onto the Form.


Gord Dibben MS Excel MVP

On Sat, 14 Feb 2009 12:37:02 -0800, Tondos
wrote:

Dave,

My question is in relation to this older posting about expanding dropdown
lists to include all choices, not just 8-12.

I'm currently using your 'myNavigator' code to create a floating toolbar
that opens each time I start Excel. The toolbar has a dropdown in it with
the names of all worksheets in the open workbook. How do I expand the
dropdown list so that all 60-70 worksheet names appear WITHOUT needing to
scroll?
Thanks.
Tondos

"Dave Peterson" wrote:

If it's a combobox from the control toolbox toolbar, you can go into design mode
(another icon on that control toolbox toolbar), then click the properties icon.

Look for ListRows and change it to what you want.


If it's a dropdown from the Forms toolbar:
Rightclick on it and choose Format Control.
On the Control tab, change the drop down lines to what you want.


If it's a data|validation dropdown, you're stuck with what excel gives you.


theslaz wrote:

I had to check. I believe it is a combo box!

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35320

--

Dave Peterson