Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort items in combobox (2000 sr-1)

    after the list of items in combobox is complete ho to order the items in combobox?
    Private Sub UserForm_Initialize()
    Dim MY_RANGE As Range
    Dim ULTIMA As String
    ULTIMA = Sheets("L0951").Cells(65536, 8).End(xlUp).Row '+ 1
    Set MY_RANGE = Sheets("L0951").Range("H3:H" & ULTIMA)
    PopulateListboxUnique ComboBox1, MY_RANGE
    'Call ORDER_ITEMS
    End Sub
    Function PopulateListboxUnique(ByRef TheListbox As Object, ByVal TheRange As Range)
    Dim TempArr(), i As Long, Cnt As Long, CLL As Range
    Cnt = 0
    ReDim TempArr(0)
    Set TheRange = Intersect(TheRange, TheRange.Parent.UsedRange)
    If TheRange Is Nothing Then Exit Function
    For Each CLL In TheRange.Cells
    If Trim(CLL.Text) <> vbNullString Then
    For i = 0 To Cnt - 1
    If TempArr(i) = CLL.Text Then Exit For
    Next
    If i = Cnt Then
    ReDim Preserve TempArr(Cnt)
    TempArr(Cnt) = CLL.Text
    Cnt = Cnt + 1
    End If
    End If
    Next
    If Cnt > 0 Then TheListbox.List = TempArr
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort items in combobox (2000 sr-1)

    Immediately below the last Next, insert this code:= to sort the array:

    Dim j As Long
    Dim tmp As String
    For i = LBound(TempArr) To UBound(TempArr) - 1
    For j = i + 1 To UBound(TempArr)
    If TempArr(i) > TempArr(j) Then
    tmp = TempArr(i)
    TempArr(i) = TempArr(j)
    TempArr(j) = tmp
    End If
    Next j
    Next i

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort items in combobox (2000 sr-1)

    MITHICS!!!!
    Work super fine!
    Tks

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort items in combobox (2000 sr-1)

    Is possible to sort the item in this function...
    Private Function GetUniqueTipoSosps_1(ByVal FIL As String, ByVal FIL2 As String) As Variant

    Set WS = Worksheets("L0951")

    If WS.FilterMode Then
    WS.ShowAllData
    End If

    Set Work = New Collection
    With Worksheets("L0951")
    On Error Resume Next
    For Each CELL In Intersect(.[H3:H65536], .UsedRange)
    If CELL = FIL2 Then
    If CELL.EntireRow.Cells(1, "L") = FIL Then
    Work.Add CELL.EntireRow.Cells(1, "J"), CStr(CELL.EntireRow.Cells(1, "J"))
    End If
    End If
    Next CELL

    On Error GoTo 0
    End With

    ReDim Result_1(1 To Work.COUNT)
    For INDEX = 1 To Work.COUNT
    Result_1(INDEX) = Work(INDEX)
    Next INDEX

    GetUniqueTipoSosps_1 = Result_1

    End Function

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort items in combobox (2000 sr-1)

    You could rewrite the code to use an array instead of a collection, like the UserForm_Initialize code from the first post in this thread.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort items in combobox (2000 sr-1)

    ok teacher!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •