Userform ListBox: What difference does it make wether I populate with .AddItem or with an array?

[dourpil]

Hi everyone!

I just tried populating a userform using this piece of code:

Set rFilter = ThisWorkbook.Worksheets("data").ListObjects("Table5").DataBodyRange
With Sheet2
  If Not .AutoFilterMode Then .Range("A1").AutoFilter
  If Not ligne = "" Then
  rFilter.AutoFilter field:=2, Criteria1:=ligne
  End If
  If Not code = "" Then
  rFilter.AutoFilter field:=3, Criteria1:=code
  End If
  If Not lot = "" Then
  rFilter.AutoFilter field:=4, Criteria1:=lot
  End If
  

  On Error GoTo attheend 'exit sub if the following line creates an error (occurs when there is no cell left after filtering
  Set Rng = rFilter.Cells.SpecialCells(xlCellTypeVisible)
  For Each Rw In Rng.Rows
  c = c + 1
  ReDim Preserve MyData(1 To Rng.Columns.Count, 1 To c)
  For Each Cell In Rw.Cells
  r = r + 1
  MyData(r, c) = Cell.Value
  Next Cell
  r = 0
  Next Rw
  Me.LbPreview.List = WorksheetFunction.Transpose(MyData)
End With

Rng, Rw, Cell, rFilter are dimmed as Range; c and r as long, MyData() as Variant. That piece of code is something I adapted from an online finding.

This code filters a table according to 3 possible filters. It then fills the listbox with what's left from the filtered table. It works nicely until in another sub I try to get the values.
Whenever my code contains something like

Me.LbPreview.List(1, 0).Value

, I get an error 424 "Object required".

Could you kindly explain to me what's happening here? I usually fill my listboxes with .AddItem; however here I have >10 columns and was thus obligated to fill my listbox that way.

Thanks!

 

[Hui]

There should be no reason to use one method over the other it really depends on how your data is laid out

You can add multiple columns using additems method.
In fact i did it last week in another post where 3 columns are added from a filtered list. The technique is shown in this post:
http://chandoo.org/forum/threads/dynamic-range-combobox-show-multiple-columns.22462/#post-135923

Can you post your file and we can have a specific look at the problem

 

[dourpil]

Thanks for your reply. Here's the file. It's a bit messy still !

What should work is: using the listboxes on the left to filter the big listbox on the right.
Selecting a row in the right-side listbox should allow the following:
When entering values in the small textboxes below and then clicking "OK", it should replace the last 10 values of the selected ListBox row.

Edit: Actually it seems my filtering will not work if you use the bottom-left ListBox. It results in a non-transposed listbox because there's only 1 corresponding row. Not sure what causes that

 

[Hui]

Have a look at the code in the link & file I attached to the last post
It does what you want
But only with 3 columns

 

[dourpil]

I might be missing something (sorry if that's the case) but it seems you're filling the combobox using the .AddItem property.

However, as I read in many forum posts (and experienced for myself when trying to fill a >10column listbox), "For an unbound data source, there is a 10-column limit (0-9)". Thus, preventing me from using .AddItem

From what I understand in the thread you linked for me, it won't work because of that. But again, I might be missing something?

 

[Hui]

Your correct
You can't have more than 10 columns in a List/Combobox which is unbound

You could think about using an Advanced filter to setup a filtered list in a temporary location which you could then use to link the Combo box to

 

[dourpil]

In case anyone gets here looking for a solution, here's how I eventually did it:

First debugging I managed was when I only had 1 row to display (i.e MyData contained only 1 column), filling the listbox bugged and it displayed the row in the form of a column. To remedy this, I replaced

Me.LbPreview.List = WorksheetFunction.Transpose(MyData)

with

Me.LbPreview.Column = MyData

And then to avoid errors while trying to change the values of my listbox, I bypassed that step and changed the values directly in my real-time-filtered table: instead of something like

 Me.LbPreview.List(x, 0).Value = Me.TbResult

which was generating an error, I pass the new value to my table with a simple

tablerange.Cells(rowposition, cycle ) = Controls("Res" & cycle).Value

and then immediately refresh the table to have it display the newly entered value(s).

EDIT: Actually that wasn't working all the time. I didn't take into account that I was modifying a filtered table & my references were thus messed up.
Changed the passing of my values to

 tablerange.Specialcells(xlcelltypevisible).Cells(rowposition, cycle )