Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a listbox when items are added
I have 2 listboxes on a userform, When I select items from one to the other
and back, the items are always showing at the bottom of the list. Is there a way to make the sort ascending, everytime a nd item is returned to its original listbox? So far, I use 2 commandbuttons cmdSelect and cmdDeselect to move items between the two listboxes. The click event codes for these buttons follows: Private Sub cmdSelect_Click() Dim Msg, Style, Title Me.ListBox2.Enabled = True For i = 0 To ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) = True And i <= 25 Then Me.ListBox2.AddItem ListBox1.List(i) ElseIf ListBox1.Selected(i) = True And i 25 Then Msg = "25 sites maximum is allowed" ' Define message. Style = vbOKOnly + vbInformation + vbDefaultButton1 ' Define buttons. Title = "Maximum Selections Allowed" ' Define title. Exit For End If Next i For i = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(i) = True Then ListBox1.RemoveItem i End If Next i End Sub Private Sub cmdDeselect_Click() For i = 0 To ListBox2.ListCount - 1 If ListBox2.Selected(i) = True Then ListBox1.AddItem ListBox2.List(i) End If Next i For i = ListBox2.ListCount - 1 To 0 Step -1 If ListBox2.Selected(i) = True Then ListBox2.RemoveItem i End If Next i If ListBox2.ListCount = 0 Then Me.cmdDeselect.Enabled = False Me.cmdDeselectAll.Enabled = False End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a listbox when items are added
The following code is a sample to sort the values in a ListBox on a
Worksheet. However, I think that it should work on a form also if you substitue the form details for the sheet details. Sub SortListBox() Dim tempVar As Variant Dim i As Integer Dim j As Integer With Sheets("Sheet1").ListBox1 For j = 0 To .ListCount - 2 For i = 0 To .ListCount - 2 If .List(i) .List(i + 1) Then tempVar = .List(i) .List(i) = .List(i + 1) .List(i + 1) = tempVar End If Next i Next j End With End Sub -- Regards, OssieMac "Ayo" wrote: I have 2 listboxes on a userform, When I select items from one to the other and back, the items are always showing at the bottom of the list. Is there a way to make the sort ascending, everytime a nd item is returned to its original listbox? So far, I use 2 commandbuttons cmdSelect and cmdDeselect to move items between the two listboxes. The click event codes for these buttons follows: Private Sub cmdSelect_Click() Dim Msg, Style, Title Me.ListBox2.Enabled = True For i = 0 To ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) = True And i <= 25 Then Me.ListBox2.AddItem ListBox1.List(i) ElseIf ListBox1.Selected(i) = True And i 25 Then Msg = "25 sites maximum is allowed" ' Define message. Style = vbOKOnly + vbInformation + vbDefaultButton1 ' Define buttons. Title = "Maximum Selections Allowed" ' Define title. Exit For End If Next i For i = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(i) = True Then ListBox1.RemoveItem i End If Next i End Sub Private Sub cmdDeselect_Click() For i = 0 To ListBox2.ListCount - 1 If ListBox2.Selected(i) = True Then ListBox1.AddItem ListBox2.List(i) End If Next i For i = ListBox2.ListCount - 1 To 0 Step -1 If ListBox2.Selected(i) = True Then ListBox2.RemoveItem i End If Next i If ListBox2.ListCount = 0 Then Me.cmdDeselect.Enabled = False Me.cmdDeselectAll.Enabled = False End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a listbox when items are added
Afterthought. If ListBox contains numerics then need to use Val function when
comparing items otherwise they sort as text and 10 will follow 1 and 20 will follow 2 etc. The following modified example for sorting numerics was tested on a UserForm. Private Sub CommandButton1_Click() 'This sample on a UserForm with numeric values in the ListBox Dim tempVar As Variant Dim i As Integer Dim j As Integer With Me.ListBox1 For j = 0 To .ListCount - 2 For i = 0 To .ListCount - 2 If Val(.List(i)) Val(.List(i + 1)) Then tempVar = .List(i) .List(i) = .List(i + 1) .List(i + 1) = tempVar End If Next i Next j End With End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a listbox when items are added
Thanks OssieMac., that did the trick.
"OssieMac" wrote: The following code is a sample to sort the values in a ListBox on a Worksheet. However, I think that it should work on a form also if you substitue the form details for the sheet details. Sub SortListBox() Dim tempVar As Variant Dim i As Integer Dim j As Integer With Sheets("Sheet1").ListBox1 For j = 0 To .ListCount - 2 For i = 0 To .ListCount - 2 If .List(i) .List(i + 1) Then tempVar = .List(i) .List(i) = .List(i + 1) .List(i + 1) = tempVar End If Next i Next j End With End Sub -- Regards, OssieMac "Ayo" wrote: I have 2 listboxes on a userform, When I select items from one to the other and back, the items are always showing at the bottom of the list. Is there a way to make the sort ascending, everytime a nd item is returned to its original listbox? So far, I use 2 commandbuttons cmdSelect and cmdDeselect to move items between the two listboxes. The click event codes for these buttons follows: Private Sub cmdSelect_Click() Dim Msg, Style, Title Me.ListBox2.Enabled = True For i = 0 To ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) = True And i <= 25 Then Me.ListBox2.AddItem ListBox1.List(i) ElseIf ListBox1.Selected(i) = True And i 25 Then Msg = "25 sites maximum is allowed" ' Define message. Style = vbOKOnly + vbInformation + vbDefaultButton1 ' Define buttons. Title = "Maximum Selections Allowed" ' Define title. Exit For End If Next i For i = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(i) = True Then ListBox1.RemoveItem i End If Next i End Sub Private Sub cmdDeselect_Click() For i = 0 To ListBox2.ListCount - 1 If ListBox2.Selected(i) = True Then ListBox1.AddItem ListBox2.List(i) End If Next i For i = ListBox2.ListCount - 1 To 0 Step -1 If ListBox2.Selected(i) = True Then ListBox2.RemoveItem i End If Next i If ListBox2.ListCount = 0 Then Me.cmdDeselect.Enabled = False Me.cmdDeselectAll.Enabled = False End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a listbox when items are added
i've found that using a hidden list and populating it from the target list,
copying in the new value at the appropriate point, is often faster than sorting when large lists are used. Another alternative is to set the source for the list as a sheet range, and the use the sort function in excel take care of soring - no point in reinventing the wheel "Ayo" wrote: I have 2 listboxes on a userform, When I select items from one to the other and back, the items are always showing at the bottom of the list. Is there a way to make the sort ascending, everytime a nd item is returned to its original listbox? So far, I use 2 commandbuttons cmdSelect and cmdDeselect to move items between the two listboxes. The click event codes for these buttons follows: Private Sub cmdSelect_Click() Dim Msg, Style, Title Me.ListBox2.Enabled = True For i = 0 To ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) = True And i <= 25 Then Me.ListBox2.AddItem ListBox1.List(i) ElseIf ListBox1.Selected(i) = True And i 25 Then Msg = "25 sites maximum is allowed" ' Define message. Style = vbOKOnly + vbInformation + vbDefaultButton1 ' Define buttons. Title = "Maximum Selections Allowed" ' Define title. Exit For End If Next i For i = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(i) = True Then ListBox1.RemoveItem i End If Next i End Sub Private Sub cmdDeselect_Click() For i = 0 To ListBox2.ListCount - 1 If ListBox2.Selected(i) = True Then ListBox1.AddItem ListBox2.List(i) End If Next i For i = ListBox2.ListCount - 1 To 0 Step -1 If ListBox2.Selected(i) = True Then ListBox2.RemoveItem i End If Next i If ListBox2.ListCount = 0 Then Me.cmdDeselect.Enabled = False Me.cmdDeselectAll.Enabled = False End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeat macro for added items | Excel Programming | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Adding Items to a ListBox-Unique Items Only | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |