Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving Mutli-Column Multiple-Selected Listbox items up or down MP Excel Discussion (Misc queries) 0 January 14th 09 09:34 PM
named range, data validation: list non-selected items, and new added items KR Excel Discussion (Misc queries) 1 June 24th 05 05:21 AM
Adding Items to a ListBox-Unique Items Only jpendegraft[_14_] Excel Programming 2 May 2nd 04 02:27 AM
Items in a Listbox Todd Huttenstine Excel Programming 1 April 26th 04 03:36 PM
Fill a ListBox with items using VBA [email protected] Excel Programming 1 April 21st 04 09:59 PM


All times are GMT +1. The time now is 09:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"