ListBox Items with Multiple Columns into Excel

[JayJay]

Well, I am stumped. Does anyone know how to take items from a listBox
that contain multiple columns (4 to be exact) and transfer them into
multiple columns in an excel worksheet? Any help would be greatly
appreciated!

Here is the code I have developed, but it only gets the first column
entered:

Private Sub OKButton_Click()
Dim lPart As Long
Dim lItem As Long
Dim CellX As Long
Dim CellW As Long
Dim CellY As Long
Dim CellZ As Long
Dim I As Long
Dim RangeRow As String
Dim ws As Worksheet
Set ws = Worksheets("Order Form")

lPart = Me.ListBox1.ListIndex
CellX = 12
RangeRow = "C"


With Me.ListBox1
For lItem = 0 To ListBox1.ListCount - 10
' If ListBox1.Selected(lPart) = True Then
If ListBox1.Selected(lItem) = True Then
CellX = CellX + 1


Range(RangeRow & CellX).Value = ListBox1.List(lItem, 1)

End If
Next lItem
End With
Unload SEARCH
End Sub



*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

Maybe this will help:

Option Explicit
Private Sub OKButton_Click()
Dim rCtr As Long
Dim cCtr As Long
Dim DestCell As Range
Dim ws As Worksheet

Set ws = Worksheets("Order Form")
Set DestCell = ws.Range("C12")

With Me.ListBox1
For rCtr = 0 To .ListCount - 1
If .Selected(rCtr) = True Then
For cCtr = 0 To .ColumnCount - 1
DestCell.Offset(0, cCtr - 1).Value = .List(rCtr, cCtr)
Next cCtr
Set DestCell = DestCell.Offset(1, 0)
End If
Next rCtr
End With

Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Sheet2")
Set myRng = .Range("a1:d" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ListBox1
.ColumnCount = 4
.List = myRng.Value
.MultiSelect = fmMultiSelectMulti
End With
End Sub

 

[Tim Zych]

This dumps the entire list out.

With Me.ListBox1
Range("C12").Resize(.ListCount, .ColumnCount).Value = .List
End With

Or selected items one by one:

Private Sub CommandButton1_Click()
Dim r As Long, c As Long, rowindex As Long
With Me.ListBox1
For r = 0 To .ListCount - 1
If .Selected(r) = True Then
rowindex = rowindex + 1
For c = 0 To .ColumnCount - 1
Range("C12")(rowindex, c + 1).Value = .List(r, c)
Next
End If
Next
End With
End Sub

 

[JayJay]

Thank You very much Tim! I was wracking my brain for hours trying to
get this! Very much appreciated! Keep up the great work!



*** Sent via Developersdex http://www.developersdex.com ***