code to parse a multi-colum multi-select listbox

J

Jade

Hello Everyone,

I need some help. I need to develop code to parse a multi-column
multi-select listbox and put the values in a worksheet.

This is the code I have but it's only giving me the last item selected
and I need to capture all items selected in the multi-select, multi-
column Listbox.

With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
UserForm1.Spreadsheet1.Range("a65536").End(xlUp) = sPrompt
& .Column(0, i)
UserForm1.Spreadsheet1.Range("B65536").End(xlUp) = sPrompt
& .Column(1, i)
End If
Next i

Again, any help would be great.
 
D

Dave Peterson

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()
Dim sPrompt As String
Dim iCtr As Long
Dim cCtr As Long
Dim destcell As Range
Dim wks As Worksheet

sPrompt = "a-"
Set wks = Worksheets("sheet2")

With wks
Set destcell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Me.ListBox2
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
For cCtr = 0 To .ColumnCount - 1
destcell.Offset(0, cCtr).Value = sPrompt & .List(iCtr, 0)
Next cCtr
Set destcell = destcell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("a1:b3")
With Me.ListBox2
.ColumnCount = myRng.Columns.Count
.MultiSelect = fmMultiSelectMulti
.List = myRng.Value
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top