Sorting Listbox items

[mikeymay]

I have two listboxes

1. Items for Selection (this is a multi select)
2. Items Selected

When all the required items are selected the user will click a button to send them to the Items Selected Listbox.

When they appear in the Selected listbox I need them to be in alphabetical order and as I am looping through the for Selection items in reverse order, they appear in the Selected listbox in reverse order and I need them to be in alpha order.

Also, if a Selected item is sent back to the For Selection listbox, how do I get the For Selection listbox into alpha order when an item is sent back?


TIA

 

[Fluff]

Can you post the code you are currently using.

 

[mikeymay]

This is the code I'm using when I want to move the selected items to the 2nd listbox

Private Sub btnSelect_Click()

Dim strItem As String

Dim intSelected As Integer

For intSelected = lbForSelection.ListCount - 1 To 0 Step -1
   If lbForSelection.Selected(intSelected) Then
      
      strItem = lbForSelection.List(intSelected)
      
      lbSelected.AddItem strItem
      
      lbForSelection.RemoveItem (intSelected)
      Else
   End If
Next

End Sub

 

[mikeymay]

So I've managed to cobble together a fix for this....

Private Sub btnSelect_Click()

Dim rngSelected As Range
Dim rngSort As Range

Dim strItem As String

Dim intSelected As Integer

Set rngSelected = Range("Holding_SelectedStart").Offset(1, 0)

For intSelected = lbForSelection.ListCount - 1 To 0 Step -1
   If lbForSelection.Selected(intSelected) Then
      
      strItem = lbForSelection.List(intSelected)
            
      rngSelected = strItem

      Set rngSelected = rngSelected.Offset(1, 0)
      
      lbForSelection.RemoveItem (intSelected)
      Else
   End If
Next

Set rngSort = Range(Range("Holding_SelectedStart").Offset(1, 0), Range("Holding_SelectedStart").Offset(Range("Holding_SelectedCount"), 0))

With Sheets("Holding").Sort
   .SortFields.Add2 Key:=Range(rngSort.Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

   .SetRange Range(rngSort.Address)
   
   .Header = xlNo
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin

   .Apply
   
   .SortFields.Clear
End With

Set rngSort = Nothing

Set rngSelected = Range("Holding_SelectedStart").Offset(1, 0)

Do Until rngSelected = ""
   lbSelected.AddItem rngSelected.Value
   
   Set rngSelected = rngSelected.Offset(1, 0)
Loop

Set rngSelected = Nothing

End Sub

 

[Fluff]

How about

Private Sub btnSelect_Click()
   Dim strItem As String
   Dim intSelected As Integer
   Dim Lst As Object
   
   Set Lst = CreateObject("system.collections.arraylist")
   For intSelected = lbForSelection.ListCount - 1 To 0 Step -1
      If lbForSelection.Selected(intSelected) Then
         Lst.Add lbForSelection.List(intSelected)
         lbForSelection.RemoveItem (intSelected)
      End If
   Next intSelected
   Lst.Sort
   lbSelected.List = Lst.toarray

End Sub

 

[mikeymay]

I'll give that a go.


Thanks

 

[mikeymay]

I have plugged this in for all the select/unslect controls and have the following

Private Sub btnSelect_Click()

Set ObjList = CreateObject("system.collections.arraylist")

For intSelected = lbForSelection.ListCount - 1 To 0 Step -1
   If lbForSelection.Selected(intSelected) Then
      ObjList.Add lbForSelection.List(intSelected)
      lbForSelection.RemoveItem (intSelected)
      Else
   End If
Next intSelected
   
ObjList.Sort

lbSelected.List = ObjList.toarray

Set ObjList = Nothing

End Sub

-------------------------------------

Private Sub btnUnSelect_Click()
   
Set ObjList = CreateObject("system.collections.arraylist")

Set ObjList = CreateObject("system.collections.arraylist")
   
For intSelected = 0 To lbForSelection.ListCount - 1
   ObjList.Add lbForSelection.List(intSelected)
Next intSelected
   
For intSelected = lbSelected.ListCount - 1 To 0 Step -1
   If lbSelected.Selected(intSelected) Then
      ObjList.Add lbSelected.List(intSelected)
      
      lbSelected.Selected(intSelected) = False
      
      lbSelected.RemoveItem (intSelected)
      Else
   End If
Next intSelected

ObjList.Sort

lbForSelection.Clear

lbForSelection.List = ObjList.toarray

Set ObjList = Nothing

End Sub

-------------------------------------------------------

Private Sub btnUnselectAll_Click()

Set ObjList = CreateObject("system.collections.arraylist")

For intSelected = 0 To lbForSelection.ListCount - 1
   ObjList.Add lbForSelection.List(intSelected)
Next intSelected

For intSelected = 0 To lbSelected.ListCount - 1
   ObjList.Add lbSelected.List(intSelected)
Next intSelected

lbSelected.Clear

ObjList.Sort

lbForSelection.Clear

lbForSelection.List = ObjList.toarray

Set ObjList = Nothing

End Sub

Thanks for your input - much more elegant than my solution!!!

 

[Fluff]

You're welcome & thanks for the feedback