Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Selected Items into an Array
Due to the range of cells, which can exceed the length of Columns(256) I have a listbox display the range of unique items, then have the user select the items for column headers. I use an array to populate the ListBox-works fine!, however I can't seem to get the selected items to show in a Debug.Print array(i) statement, it keeps showing nothing like the array is empty. Does anybody have a code snippet, that works better than mine Option Explicit Dim myList() As Variant Dim count1 As Integer Private Sub cmdMovetoRight_Click() Dim i As Integer If ListBox1.ListIndex = -1 Then Exit Sub For i = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i) ListBox1.RemoveItem i End If If i = ListBox1.ListCount Then Exit Sub End If Next i End Sub Private Sub cmdSave_Click() Dim i, j As Integer count1 = ListBox2.ListCount With Me.ListBox2 For i = 0 To .ListCount - 1 ReDim myList(count1) myList(count1) = .List(i) Debug.Print myList(count1) Next i End With For j = 0 To count1 - 1 MsgBox myList(j) Next j Unload Me End Sub Maybe the MsgBox is a bit redundant. Suggestions would be appreciated. All I need to due is take the values of myList and used the selected Items in columns headings so I don't exceed the 256 column limit. Thanks; John -- jtp550 ------------------------------------------------------------------------ jtp550's Profile: http://www.excelforum.com/member.php...fo&userid=5789 View this thread: http://www.excelforum.com/showthread...hreadid=471079 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Selected Items into an Array
For i = 0 To .ListCount - 1
ReDim myList(count1) myList(count1) = .List(i) Debug.Print myList(count1) Next i should be For i = 0 To .ListCount - 1 ReDim Preserve myList(count1) myList(count1) = .List(i) Debug.Print myList(count1) Next i Regards, Tom Ogilvy "jtp550" wrote in message ... Due to the range of cells, which can exceed the length of Columns(256) I have a listbox display the range of unique items, then have the user select the items for column headers. I use an array to populate the ListBox-works fine!, however I can't seem to get the selected items to show in a Debug.Print array(i) statement, it keeps showing nothing like the array is empty. Does anybody have a code snippet, that works better than mine Option Explicit Dim myList() As Variant Dim count1 As Integer Private Sub cmdMovetoRight_Click() Dim i As Integer If ListBox1.ListIndex = -1 Then Exit Sub For i = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i) ListBox1.RemoveItem i End If If i = ListBox1.ListCount Then Exit Sub End If Next i End Sub Private Sub cmdSave_Click() Dim i, j As Integer count1 = ListBox2.ListCount With Me.ListBox2 For i = 0 To .ListCount - 1 ReDim myList(count1) myList(count1) = .List(i) Debug.Print myList(count1) Next i End With For j = 0 To count1 - 1 MsgBox myList(j) Next j Unload Me End Sub Maybe the MsgBox is a bit redundant. Suggestions would be appreciated. All I need to due is take the values of myList and used the selected Items in columns headings so I don't exceed the 256 column limit. Thanks; John -- jtp550 ------------------------------------------------------------------------ jtp550's Profile: http://www.excelforum.com/member.php...fo&userid=5789 View this thread: http://www.excelforum.com/showthread...hreadid=471079 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Selected Items into an Array
Hi Tom; Thanks for the quick response, sometimes you can't see the forest for the trees Work's now! John -- jtp550 ------------------------------------------------------------------------ jtp550's Profile: http://www.excelforum.com/member.php...fo&userid=5789 View this thread: http://www.excelforum.com/showthread...hreadid=471079 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Selected Items into an Array
Tom; I missed the part where you were confused about the 256 columns Briefly I import an acsii file from our Coordinate Measurin Machine(X,Y,Z values(decimal)) and sort them out for statistics purposes. There can be on a monthl basis between 10 to 50 different parts, with as many as 500 dimension checked. So, when I mentioned about sorting the unique values, transpose them from rows to columns with the number of parts going dow in rows(usually 30 samples per part number). I can send you a copy o what I am doing, if you like. Sincerely; Joh -- jtp55 ----------------------------------------------------------------------- jtp550's Profile: http://www.excelforum.com/member.php...nfo&userid=578 View this thread: http://www.excelforum.com/showthread.php?threadid=47107 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Mutli-Column Multiple-Selected Listbox items up or down | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Adding Items to a ListBox-Unique Items Only | Excel Programming | |||
Items in a Listbox | Excel Programming | |||
Fill a ListBox with items using VBA | Excel Programming |