Sleeper: Multiple Value List Box

[sheeeng]

How do I copy all multiple selected string value in a List Box into Excel Worksheet?

For example, to copy all multiple selected names in the list box to a column in "Sheet1"?

Thanks.

[Norie]

Where is the listbox located?

[sheeeng]

UserForm1.ListBox1

How to use .Selected or something else to read selected data?

[BlueCactus]

How about something like this (untested):

Dim i as integer, seltotal as integer, temp as variant
' Count number of selections in ListBox1
' I prefer this to using ReDim Preserve on each loop cycle
seltotal = 0
    For i = 0 To ListBox.ListCount - 1
       If ListBox1.Selected(i) Then seltotal = seltotal + 1
    Next i
' Stuff an array with all of the selections
ReDim temp(1 to seltotal, 1 to 1)
seltotal = 1
for i = 0 to ListBox1.ListCount - 1
if ListBox1.Selected(i) then
temp(seltotal, 1) = ListBox1.List(i)
seltotal = seltotal + 1
end if
next i
' Dump the array to ActiveSheet
ActiveSheet.Cells(1,1).Resize(ubound(temp,1),ubound(temp,2)) = temp

If you have multiple columns in the listbox, you'll have to include a column number in .List().

[sheeeng]

The code still not work. How?
How to save selected string in the ListBox to an array?
How do I filter the data in listbox using text entered in TextBox1?
eg. enter "o" will focus on first string start with "o" in ListBox.

Thanks.

[BlueCactus]

Oops, that's supposed to be

For i = 0 to ListBox1.ListCount

(missing the '1' in the code above.)

I still haven't tested the code, so if it's not working let me know what happened.

Do you have multiple columns in ListBox1?

[sheeeng]

no multiple columns. thanks. I had already put inthe '1'. Working but copy all to the Sheet without control.

[BlueCactus]

no multiple columns. thanks. I had already put inthe '1'. Working but copy all to the Sheet without control.

What control is lacking?

[sheeeng]

Copy all the data without string compare.

[BlueCactus]

OK, maybe I'm misunderstanding this. Let me tell you what I think you were asking for, and then you can correct me.

You have a ListBox on a userform. It contains multiple items, and allows for selecting multiple items. You want to copy all of the user-selected items (to the worksheet), leaving behind all of the items that are not selected. For example:

LISTBOX		      SELECTED       SHEET
A			yes	    A
B			 no	      D
C	---> 	        no     -->    F
D			yes
E			no
F			yes

[Norie]

Copy all the data without string compare.

What 'string compare' are you referring to?

[sheeeng]

BlueCactus, you are almost right! Thanks.

Let me layout my need in this VBA sub.

But before copy to Sheet2, it must check whether .Selected exist in sheet1. If exist in Sheet1, then copy it to sheet2.


Thanks a lot.

[sheeeng]

New question here. Can anyone show me how to built a 5/6 column Multiple Column List Box?

Thanks.

[Bob Phillips]

New question here. Can anyone show me how to built a 5/6 column Multiple Column List Box?

You need to set the columncount property and load all columns. This can be done from a range, set the RowSource property to that 2D range, or by loading from an array

ReDim ary(1 To 2, 1 To 5)
For i = 1 To 2
For j = 1 To 5
ary(i, j) = i * j
Next j
Next i
With ListBox1
For i = 1 To 2
.AddItem ary(i, 1)
For j = 2 To 5
.List(.ListCount - 1, j - 1) = ary(i, j)
Next j
Next i
End With

or directly

ListBox1.List = ary

If your data is in a Recordset, you can convert this to an array and load

ary = RS.GetRows() 
With Me.ListBox1 
.ColumnCount = 3 
.List = ary 
End With

And to get at the data, use

With Me.ListBox1 
        MsgBox .List(.ListIndex, 0) & ", " & _ 
             .List(.ListIndex, 1) & ", " & _ 
             .List(.ListIndex, 2) 
    End With