ComboBox won't display more than 1000 items in Excel 2007

[jkampfe]

I have an application that creates a command bar combobox in Excel with about
4800 items. I have been running this application for years without a problem
under Excel 2000 or 2003. When running it in 2007 the ComboBox will not
display more than 1000 items. Running a debug trace shows everything to be
behaving normally. Items are added to the list using a For/Next loop without
error and the ListCount property shows the correct number of items (4821 to
be exact) but only the first 1000 items are displayed. Has anyone come
accross this? Any help would be appreciated. Thanks.



James Kampfe



Here is the section of code used to populate the ComboBox:



Dim RouterListBox As CommandBarControl
Set RouterListBox = CommandBars("EstimateToolbar").Controls.Add _
(Type:=msoControlComboBox)
With RouterListBox
.Caption = "Router"
.OnAction = "PasteRouter"
.Style = msoComboNormal
.Width = 200
.BeginGroup = True
.TooltipText = "Select a Router Number"

For x = 0 To RouterCount
.AddItem ActiveCell.Offset(x, 0).Value
Next x

.ListIndex = 0
End With

 

[Chip Pearson]

I haven't run across that myself, but I, for one, would not like to work
with a program that had 4800 items in a combo box. You should consider
redesigning your user interface.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Keith R]

I have XL2007 but haven't installed it yet based on all the issues that have
been reported, but I'll chime in because I can give an example of when >1000
entries might make sense (at least in my twisted world of amateur
programming- Chip, I agree that overall it isnt good design but I can't
think of a good workaround, and welcome any suggestions on alternatives). In
addition to (my apologies) hijacking this thread, I'll also be looking for
any info on getting around the 1000 entry limit.

In XL2003 I have a userform with a combobox and three buttons; the first is
"direct reports" which lists up to maybe 30 people max, "my tree" which
allows managers of managers to see all employees that report through them
(number varies by level in the organization), and "show all" which shows up
to 3100 people. A manager needs to be able to find any person to log
appropriate data and keep our company in regulatory compliance. The biggest
downside for me is that I'd prefer to have the combobox to match an entry,
e.g. if I type Fie then it might match F- Faber, Fi-Fidrow, Fie-Field to get
close in the list, whereas now it rematches with each letter, e.g. F-Faber,
I-Icarda, E-Easton (etc).

If we couldn't list all of the people in the organization, then I'd have to
come up with some other equally inelegant, and slighlty more complicated
solution, such as having users select the first letter of the last name in
one combobox (26 entries) and have that populate a second combobox with all
last names starting with that letter. That adds controls to the interface,
and mouseclicks- it would be so much nicer to have everything in one place
(and allow the matched entry).

Thanks,
Keith

 

[Meera]

I came across the same problem yesterday. We also have a application that
creates a command bar combobox in Excel with about more than 5000 account
numbers. It was working for all this years without a problem under excel
2000 or 2003. When running in 2007 the combo box will not display more than
1000 items. Any help is appreciated. Thanks.

Meera