Consulting

Results 1 to 3 of 3

Thread: Sorting a multicolumn listbox

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    5
    Location

    Sorting a multicolumn listbox

    Hello,

    I have a multi-column listbox with 3 columns. I would like to sort the items in thelistbox from in ascending order (A toZ) based on the items in column one. I'm using the following code to sort the items but it is only sorting theitems in the first column. I would likefor columns two and three to be sorted as well matched up with the sort that took place incolumn one. Can this be down with amulti-column listbox with three columns?

    Private Sub CommandButton1_Click()
    'Sorts ListBox List
    Dim i As Long
    Dim j As Long
    Dim temp AsVariant

    With Me.ListBox1
    For j = 0 ToListBox1.ListCount - 2
    For i = 0To ListBox1.ListCount - 2
    If.List(i) > .List(i + 1) Then
    temp = .List(i)
    .List(i) = .List(i + 1)
    .List(i + 1) = temp
    End If
    Next i
    Next j
    End With
    End Sub

    regards,

    Darryl R. Moore

  2. #2
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    5
    Location
    Found my answer:

    Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)
    Dim vaItems As Variant
    Dim i As Long, j As Long
    Dim c As Integer
    Dim vTemp As Variant

    'Put the items in a variant array
    vaItems = oLb.List

    'Sort the Array Alphabetically(1)
    If sType = 1 Then
    For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
    For j = i + 1 To UBound(vaItems, 1)
    'Sort Ascending (1)
    If sDir = 1 Then
    If vaItems(i, sCol) > vaItems(j, sCol) Then
    For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
    vTemp = vaItems(i, c)
    vaItems(i, c) = vaItems(j, c)
    vaItems(j, c) = vTemp
    Next c
    End If

    'Sort Descending (2)
    ElseIf sDir = 2 Then
    If vaItems(i, sCol) < vaItems(j, sCol) Then
    For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
    vTemp = vaItems(i, c)
    vaItems(i, c) = vaItems(j, c)
    vaItems(j, c) = vTemp
    Next c
    End If
    End If

    Next j
    Next i
    'Sort the Array Numerically(2)
    '(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)
    ElseIf sType = 2 Then
    For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
    For j = i + 1 To UBound(vaItems, 1)
    'Sort Ascending (1)
    If sDir = 1 Then
    If CInt(vaItems(i, sCol)) > CInt(vaItems(j, sCol)) Then
    For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
    vTemp = vaItems(i, c)
    vaItems(i, c) = vaItems(j, c)
    vaItems(j, c) = vTemp
    Next c
    End If

    'Sort Descending (2)
    ElseIf sDir = 2 Then
    If CInt(vaItems(i, sCol)) < CInt(vaItems(j, sCol)) Then
    For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
    vTemp = vaItems(i, c)
    vaItems(i, c) = vaItems(j, c)
    vaItems(j, c) = vTemp
    Next c
    End If
    End If

    Next j
    Next i
    End If

    'Set the list to the array
    oLb.List = vaItems
    End Sub

    You would run it by calling the procedure like this:
    Run "SortListBox",
    [ListBox Name],
    [ListBox column to sort by],[Alpha(1) or Numeric(2) Sort],[Ascending(1) or Descending(2) Order]


    Code:
    'Sort by the 1st column in the ListBox Alphabetically in Ascending Order
    Run "SortListBox", ListBox1, 0, 1, 1

    'Sort by the 1st column in the ListBox Alphabetically in Descending Order
    Run "SortListBox", ListBox1, 0, 1, 2

    'Sort by the 2nd column in the ListBox Numerically in Ascending Order
    Run "SortListBox", ListBox1, 1, 2, 1

    'Sort by the 2nd column in the ListBox Numerically in Descending Order
    Run "SortListBox", ListBox1, 1, 2, 2

  3. #3
    VBAX Regular
    Joined
    Sep 2016
    Posts
    37
    Location
    Darryl, where you placing your 'Run' code?

    Also, does this have columns limit and how does it handle numbers greater then 9 (ie: 10, 11, 20, 21, 30)? I have being having issue with sorting double digits, I'll get; 1, 10, 11, 2, 3...


    Nimesh

Tags for this Thread

Posting Permissions

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