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 - 13 Mar 2008 21:20 GMT
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.
> EXCEL 2003
> I am filling 2 listboxes. The first listbox is filled like this:
[quoted text clipped - 18 lines]
> thank you,
> Steve
Dave Peterson - 13 Mar 2008 21:24 GMT
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
> EXCEL 2003
> I am filling 2 listboxes. The first listbox is filled like this:
[quoted text clipped - 18 lines]
> thank you,
> Steve

Signature
Dave Peterson