2 More ListBox Questions

Posted by JAF on July 28, 2000 3:58 AM

Question 1 - Is it possible to sort items added to a ListBox into ascending alphabetical order, and if so, what's the code to do it?

Question 2 - I have a list box which has items added to it by the user and I want to take the values in the ListBox (a MultiSelect ListBox where the items may or may not be selected/highlighted) and put those values into Column C on my spreadsheet.

Example
ListBox1 contains ABC1, ABC2, ABC3, ABC4 and ABC5. User selects ABC1, ABC3 and ABC4 and adds them to ListBox2. What I need is for Column C (Rows 2 through to 4) to fill in ABC1, ABC3 and ABC4 respectively. The actual number of items in ListBox 2 will vary from a minimum of 1 up to a maximum of 27 (although that number may change in the future).

Any suggestions?

JAF

Posted by Ivan Moala on July 28, 0100 1:55 PM

Jaf
This routine will add in alha order
Note: in this example it is attached to a command button.
You will have to adapt if you don't want it attached.

Sub Selectionsort(values() As String, ByVal min As Long, ByVal max As Long)
Dim i As Long
Dim j As Long
Dim smallest_value As String
Dim smallest_j As Long

For i = min To max - 1
' Find the smallest remaining value in entries
' i through num.
smallest_value = values(i)
smallest_j = i

For j = i + 1 To max
' See if values(j) is smaller.
If values(j) &LT; smallest_value Then
' Save the new smallest value.
smallest_value = values(j)
smallest_j = j
End If
Next j

If smallest_j &LT;&GT; i Then
' Swap items i and smallest_j.
values(smallest_j) = values(i)
values(i) = smallest_value
End If

Next i

End Sub

Private Sub CommandButton1_Click()
Dim values() As String
Dim num_items As Integer
Dim i As Integer

' Put the list choices in a string array.
num_items = ListBox1.ListCount

ReDim values(1 To num_items)

For i = 1 To num_items
values(i) = ListBox1.List(i - 1)
Next i

' Sort the list.
Selectionsort values, 1, num_items

' Put the items back in the ListBox.
Me.ListBox1.RowSource = ""
Me.ListBox1.Clear
For i = 1 To num_items
Me.ListBox1.AddItem values(i)
Next i
'Call SortListBox(ListBox1)
End Sub

For this just loop through your selected = True
items and paste value to your range, incrementing
the range as you proceed in the loop.

HTH

Ivan