Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
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) < smallest_value Then
' Save the new smallest value.
smallest_value = values(j)
smallest_j = j
End If
Next j
If smallest_j <> 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