Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox won't display more than 1000 items in Excel 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox won't display more than 1000 items in Excel 2007
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) "jkampfe" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox won't display more than 1000 items in Excel 2007
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 "Chip Pearson" wrote in message ... 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) "jkampfe" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox won't display more than 1000 items in Excel 2007
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 "Keith R" wrote: 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 "Chip Pearson" wrote in message ... 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) "jkampfe" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display top ten items in an Office 2007 pivot table | Excel Discussion (Misc queries) | |||
How do I see more than 1000 autofilter drop down items | Excel Discussion (Misc queries) | |||
Excel 2007- formula =SUM(850*77.1) returns incorrect answer = 1000 | Excel Discussion (Misc queries) | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Adding items to a ComboBox in Excel VBA | Excel Programming |