PDA

View Full Version : Sleeper: Multiple Value List Box



sheeeng
06-08-2005, 12:28 AM
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. :hi:

Norie
06-08-2005, 05:40 AM
Where is the listbox located?

sheeeng
06-08-2005, 07:41 AM
UserForm1.ListBox1

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

BlueCactus
06-08-2005, 08:55 AM
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
06-08-2005, 09:07 PM
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. :friends:

BlueCactus
06-08-2005, 11:49 PM
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
06-09-2005, 12:23 AM
no multiple columns. thanks. I had already put inthe '1'. Working but copy all to the Sheet without control.

BlueCactus
06-09-2005, 06:35 AM
no multiple columns. thanks. I had already put inthe '1'. Working but copy all to the Sheet without control.
What control is lacking?

sheeeng
06-09-2005, 07:09 AM
Copy all the data without string compare.

BlueCactus
06-09-2005, 09:48 AM
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
06-09-2005, 10:29 AM
Copy all the data without string compare.
What 'string compare' are you referring to?

sheeeng
06-09-2005, 07:08 PM
BlueCactus, you are almost right! :hi: 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. :friends: :rotlaugh:

sheeeng
06-21-2005, 09:38 PM
New question here. Can anyone show me how to built a 5/6 column Multiple Column List Box?

Thanks.

Bob Phillips
06-22-2005, 01:19 AM
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