+ Reply to Thread
Results 1 to 6 of 6

Can't add over 9 items to a list item

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Can't add over 9 items to a list item

    I am evaluating rows in a table.

    Going row by row, if a condition is met, I wish to first additem from the first column, then to that item add 12 additional cell values in the following 12 columns.

    To eliminate any chance of it having to do with the nature of the cell value, I reduced the data to simple numerical, as shown below.

    All runs fine for the first 10 items (the first by additem, then the next 9 by subsequent assignment as shown below) added to the listcount, but no matter what values I try for the next 3 it fails (on number 10). This was verified by when it failed and I selected debug I examined frmRelRecs.lbxRelRecs.List(0,9) in the immediate window and the value was 9.

    Please Login or Register  to view this content.
    I have my column count set to 13, even tried 20, but no difference.

    Can someone please tell me what am I messing up on?

    Many thanks!!!

    p.s.: I was considering running an advanced filter on the table, copying it to a temp sheet and using the databody as my rowsource, but for some reason I chose this direction...

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Can't add over 9 items to a list item

    You're not crazy. There is a ten column limit using this method (adding column values individually).

    Two methods around this limit:
    1.) Use the RowSource method to assign a range to the the listbox
    2.) Put your values in an array first and then assign the array to the listbox.

    I imagine the 2nd option would work best or you. Let me know if you need help coding it.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Re: Can't add over 9 items to a list item

    Saving my sanity once again-

    Thank-you!

  4. #4
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Re: Can't add over 9 items to a list item

    AlphaFrog-

    Do you have a rule-of-thumb guide as to when to use an advanced filter to a temp worksheet and use that UsedRange to populate a combobox/listbox rather than looping through the rows and adding? Seems to me that at some point the looping would take longer. Trying to wrap my head around the pros & cons of each method-

    Thank-you!

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Can't add over 9 items to a list item

    1.) I almost never use the .RowSource method. To add\remove items in the list, you would have to add\remove items from the range. Too messy usually.

    2.) I may use the .Add .Remove methods if the list is short. Coding is usually pretty easy, but you're right, it's not very efficient if the list is long and you have reference the data on sheet many times.

    3.) My preferred method is to read the raw data into an array. Then loop through the array and filter out what I don't want. Then apply the array to the list (.List = MyArray). This an efficient method as it only requires one range reference to the worksheet to read the data en mas to an array. Looping through the data, once it's in an array, is very efficient.

    Of course, all of that is just my preference.

  6. #6
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Re: Can't add over 9 items to a list item

    I think I will kind of take the preference of a "Forum Guru" rather seriously...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. To Find an item from list items automatically.
    By mohamedcv in forum Excel General
    Replies: 4
    Last Post: 12-20-2015, 01:56 PM
  2. Replies: 15
    Last Post: 01-08-2015, 11:40 AM
  3. Making a list of items, then showing the date the item was last ordered
    By dance in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-30-2014, 06:05 AM
  4. [SOLVED] autosum/ auto calculate each item from a random list of items
    By angerion in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-30-2013, 11:23 PM
  5. Replies: 0
    Last Post: 11-13-2012, 12:53 PM
  6. [SOLVED] Items in Userform Listbox are wider than list box. Need to see all character of item
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2012, 06:40 AM
  7. Pivot Table dropping items from page item list
    By Sharon in forum Excel General
    Replies: 4
    Last Post: 04-04-2006, 03:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1