ListBox error when adding array as list

[mc84excel]

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.

[AlphaFrog]

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 ?)

[mc84excel]

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?

[JosephP]

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

[AlphaFrog]

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.

[mc84excel]

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

[Norie]

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.

[AlphaFrog]

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.

[Norie]

Add to a listbox?

I thought it was to do with populating a listbox.

[AlphaFrog]

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

[mc84excel]

Hello Norie,

Good to hear from you.

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

[mc84excel]

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).

[AlphaFrog]

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.

[mc84excel]

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.

[Norie]

Can we see this function?

[mc84excel]

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.

[JosephP]

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

[snb]

Please Login or Register  to view this content.

[mc84excel]

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).

[mc84excel]

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

[Kyle123]

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

[mc84excel]

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)

[Kyle123]

Then you need to step through,

Please Login or Register  to view this content.

works perfectly

[mc84excel]

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

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

[Kyle123]

Please Login or Register  to view this content.

[mc84excel]

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.

[JosephP]

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

[mc84excel]

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