Odd behaviour with lists and .list

[ngmp]

Hi Everyone

I am pretty new to VBA so this might be well understood (and posted elsewhere but I did look and couldn't find it so sorry if this has been answered many times)

Basically I am try to use two list boxes (ListBox1, ListBox2). ListBox2 is populated data derived from what is chosen from the list in ListBox1. The list in ListBox2 is variable and ranges from 1 item to 350 items dependent on the value picked up in ListBox1

Here's the issue when there are more than one items generated for ListBox2 everything is fine and ListBox2 is populated. But if only one item is found by choosing a certain ListBox1 value then my code crashes.

Here's the code snippet: The derived list values are placed in Row K (11), Counter is a loop counter for number of entries found for listBox2

Please Login or Register  to view this content.

This ok until Counter -1 = 1 i.e the expression is ".List = Range(Cells(1, 11),Cells(1, 11)).value" (There's a -1 because counter is counting in a loop adding +1 each condition so will come out of the loop one too high) giving Run-Time Error 381: Could not set the list property: Invalid Property Array Index

However if I "pad" the value like the code snippet below it works

Please Login or Register  to view this content.

So the .list value is two discreet cells in the sheet rather than one. The value in Cells(1,12)="" but this seems to satisfy VBA.

Can anyone tell me what's going on?

Thanks, Nick

[watersev]

hi ngmp, welcome to Excelforum, VB help file reference for List property:

Use List to copy an entire two-dimensional array of values to a control. Use AddItem to load a one-dimensional array or to load an individual element.

This code should explain what is treated as array if array is created from a sheet range:

Please Login or Register  to view this content.

[ngmp]

Hi watersev

Thank you for the welcome!

Also thank you for the reply which turned on the light bulb in my brain - as you indicated that I was making List think I was accessing a 2-dimensional array (which is blindingly obvious now you've pointed it out!!!) but in fact I wasn't meaning to. So the answer, as you point out is to use .AddItem and ListBox2.Clear to do it. Now it works fine. Thank you so much for this insight....I feel like Homer Simpson though!

Nick