Listbox Max Columns

[steve]

EXCEL 2003
I am filling 2 listboxes. The first listbox is filled like this:

lr = LastRow(ThisWorkbook.Worksheets("Mesh"))
With ThisWorkbook.Worksheets("Mesh")
Set MeshInventory = .Range(.Cells(2, "A"), .Cells(lr, "K"))
End With
With Me.lbMeshInventory
.ColumnHeads = False 'can't use this feature here
.ColumnCount = MeshInventory.Columns.Count
.List = MeshInventory.Value
End With

Since this source is bound, it lest me have more than 10 columns in the
listbox. When the user double clicks an item on the first Listbox, I want
that item added to the second listbox. I'm pulling my hair out trying to
figure out how to make the second listbox bound so it can also have more than
10 columns. Dave Peterson posted something on 3/1/07 and said to use
..AddItem, but I get an error when the code gets to the 10th column.

thank you,
Steve

 

[JLGWhiz]

I think you would have to go back to your list source to pick up all eleven
columns.
It will only pick up the bound column value or the first column value by
default as the Value source from one list box to the other. To get a row of
eleven columns you would need and array or list to load the list box.

 

[Dave Peterson]

But you didn't say that you were exceeding 10 columns in that post--I hope I
wouldn't have suggested .additem if you had included that.

If you assign an array to the .list, then you can have more than 10 columns.
Your posted code isn't really using a bound control.

..rowsource = MeshInventory.address(external:=true)

would be bound to the worksheet.

I created a small userform with 2 listboxes and 2 commandbuttons. I put some
test data in Sheet1 A1:BB10 (just the cell's address)--but way more than 10
columns.

This was behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim rCtr As Long
Dim cCtr As Long
Dim lCtr As Long
Dim myArr As Variant

rCtr = -1
With Me.ListBox1
'since you can only change the last dimension,
'this array is transposed
'columns x rows instead of rows x columns
ReDim myArr(0 To .ColumnCount - 1, 0 To .ListCount - 1)
For lCtr = 0 To .ListCount - 1
If .Selected(lCtr) = True Then
rCtr = rCtr + 1
For cCtr = 0 To .ColumnCount - 1
myArr(cCtr, rCtr) = .List(lCtr, cCtr)
Next cCtr
End If
Next lCtr

If rCtr = -1 Then
'nothing selected
Beep
Else
ReDim Preserve myArr(0 To .ColumnCount - 1, 0 To rCtr)
Me.ListBox2.List = Application.Transpose(myArr)
End If

End With
End Sub
Private Sub UserForm_Initialize()
Dim MeshInventory As Range

With ThisWorkbook.Worksheets("Sheet1")
Set MeshInventory = .Range("A2:bb" & .Cells(.Rows.Count, "K").Row)
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ColumnHeads = False 'can't use this feature here
.ColumnCount = MeshInventory.Columns.Count
.List = MeshInventory.Value
End With

With Me.ListBox2
.ColumnHeads = False
.ColumnCount = Me.ListBox1.ColumnCount
End With

With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With

With Me.CommandButton2
.Caption = "Transfer to lb2"
.Default = True
End With
End Sub