+ Reply to Thread
Results 1 to 28 of 28

ListBox error when adding array as list

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Unhappy ListBox error when adding array as list

    Hello all,

    I have a user form with a list box which is populated from a 2D array. Up to now it has been working fine. However recently it gave a error message and wouldn't populate the listbox. Upon investigating, the array in question only had 1 row, effectively making it a 1D array. How can I alter the code (extract below) so that it wont collapse when it comes across a 1D array??

    Please Login or Register  to view this content.
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

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

    Re: ListBox error when adding array as list

    How are you creating\populating varTempArray ?
    Can you make it 2D where the row has only one element?

    ReDim varTempArray(1 to 1, 1 to ?)

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by AlphaFrog View Post
    How are you creating\populating varTempArray ?
    Can you make it 2D where the row has only one element?

    ReDim varTempArray(1 to 1, 1 to ?)
    I'm creating the varTempArray by a project level function I created. Provide the argument of sheet name (in Active Workbook) and the function will automatically create an array from that.

    Appreciate your suggestion about converting 1D array to 2D. I may end up using this however I'd rather do this a different way if possible. I have a function to determine the number of dimensions in an array. I was thinking of doing something like:
    Please Login or Register  to view this content.
    How do I display a 1D array in the multi column listbox?
    Last edited by mc84excel; 05-20-2013 at 10:55 PM. Reason: correct description of function

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ListBox error when adding array as list

    perhaps transpose it twice with application.transpose or use another function to convert it to a 2d array. I reckon changing the original array population would be more sensible
    Josie

    if at first you don't succeed try doing it the way your wife told you to

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

    Re: ListBox error when adding array as list

    Quote Originally Posted by mc84excel View Post
    How do I display a 1D array in the multi column listbox?
    I would think having your Function that creates the array make 2-D arrays always would be easiest even for the case where there is only one row element.

    Otherwise, something like this...
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    So it's not possible to add a 1D array to a listbox by using .List?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox error when adding array as list

    Yes it is possible to add a 1D array with List.

    However it's not possible to add a single value using List.
    Please Login or Register  to view this content.
    Perhaps you could check if you actually have an array.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

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

    Re: ListBox error when adding array as list

    Quote Originally Posted by Norie View Post
    Yes it is possible to add a 1D array with List.
    I think the issue is to add to a multicolumn listbox from a 1D array using .List
    I could be wrong though.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox error when adding array as list

    Add to a listbox?

    I thought it was to do with populating a listbox.

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

    Re: ListBox error when adding array as list

    I think the issue is to "populate" a multicolumn listbox from a 1D array using .List

    Populate one row and multiple columns from a 1D array using .List

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Hello Norie,

    Good to hear from you.

    I confirm the 1D array has more than one value (Locals window).

  12. #12
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by AlphaFrog View Post
    I think the issue is to "populate" a multicolumn listbox from a 1D array using .List

    Populate one row and multiple columns from a 1D array using .List
    Correct. That is what I would like to achieve (although if it's not possible to do this, I will go with your alternative solution - convert 1D array to 2D).

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

    Re: ListBox error when adding array as list

    Quote Originally Posted by mc84excel View Post
    Correct. That is what I would like to achieve (although if it's not possible to do this, I will go with your alternative solution - convert 1D array to 2D).
    I don't know of a way to do that, but I don't know everything.

    You may not need to "convert" from 1D to 2D. Depending on how your function that creates the array works, it could be coded to create 2D arrays always even for one-row situations.

  14. #14
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by AlphaFrog View Post
    Depending on how your function that creates the array works, it could be coded to create 2D arrays always even for one-row situations.
    I'm trying to avoid going down that route. It is a function I use in a number of projects - in some calls to it, I want the array it returns to remain as 1D.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Can we see this function?

  16. #16
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by Norie View Post
    Can we see this function?
    Well that is diverting slightly from what I am currently trying to solve. (I want to know if it's possible to add a 1D array to a listbox by using .List).

    But sure, you can have the function. See below:
    Please Login or Register  to view this content.

  17. #17
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ListBox error when adding array as list

    does the error occur when you assign the list or when you try and set the columncount-you try to use the second dimension for that

  18. #18
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: ListBox error when adding array as list

    Please Login or Register  to view this content.



  19. #19
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by JosephP View Post
    does the error occur when you assign the list or when you try and set the columncount-you try to use the second dimension for that
    I changed the column count to 3. I don't get an error message but the list box is now empty (instead of showing 1 row x 3 cols data).

  20. #20
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by snb View Post
    Please Login or Register  to view this content.
    Thanks snb but test results = No error message but no rows appearing in ListBox (same as my reply to JosephP)

    (And yes there is a 1D array present - I checked the Locals Window. But it is not being added to the ListBox)

    UPDATE: It seems that there are issues with adding 1 row to a listbox... http://www.excelforum.com/excel-prog...recordset.html
    Last edited by mc84excel; 05-23-2013 at 09:58 PM.

  21. #21
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ListBox error when adding array as list

    Does using the .column property of the listbox rather than using .list work?

  22. #22
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by Kyle123 View Post
    Does using the .column property of the listbox rather than using .list work?
    Tested it now = No error message but no contents inside ListBox1... (same as JosephP & snb)

  23. #23
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ListBox error when adding array as list

    Then you need to step through,

    Please Login or Register  to view this content.
    works perfectly

  24. #24
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    I have had a suggestion from Nilem to upload the UserForm.

    I have uploaded a dummy test workbook instead. See attached...
    Attached Files Attached Files

  25. #25
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ListBox error when adding array as list

    Please Login or Register  to view this content.

  26. #26
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by Kyle123 View Post
    Please Login or Register  to view this content.
    BRILLIANT! Words fail me. +1

    (Mutters to self: How come it didn't work when I tried it in the original workbook? oh well, I will be able to solve it now that I have a working test copy...)

    UPDATE: I have it working in the original code. See below for anyone interested.
    A big thank you to Kyle123 for providing the solution and to JosephP for providing the explanation as to what was going wrong.

    Please Login or Register  to view this content.
    Last edited by mc84excel; 05-26-2013 at 06:45 PM. Reason: update to record solved

  27. #27
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ListBox error when adding array as list

    if you assign a 1D array to .List you get one column of data-and your first column is hidden by your code

    if you assign it to .Column as Kyle did you get one row of data

  28. #28
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: ListBox error when adding array as list

    Quote Originally Posted by JosephP View Post
    if you assign a 1D array to .List you get one column of data-and your first column is hidden by your code

    if you assign it to .Column as Kyle did you get one row of data
    INSERT LIGHTBULB SYMBOL HERE. Ah, that explains it. Thank you for a clear and precise explanation (and for solving the mystery - unsolved Excel mysteries really bug me ) +1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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