Sorting Listbox items

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Can you post the code you are currently using.
 
Upvote 0
This is the code I'm using when I want to move the selected items to the 2nd listbox
Code:
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
 
Last edited:
Upvote 0
So I've managed to cobble together a fix for this....

Code:
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
I have plugged this in for all the select/unslect controls and have the following
Code:
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!!!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top