sort data in listbox based on selected row from another listbox

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
704
Office Version
  1. 2019
Hi,
I know this way could do that by textbox or combobox and there are lots of codes about it, but I would that by another list box .
so I have about 22 sheets and every sheet could contain 5000 rows for each sheet and different structure but all of data will be from row1.
so I would populate sheets name in listbox1 and when select item then sort data and show in listbox2.
here is sample data
Microsoft Excel‫‬.xlsx
ABCDEF
1ITEMCODEBRANDQTYUNIT PRICETOTAL
211221BS 205/70R15C R623 THI62.00405.0025,110.00
321227BS 215/70R15C R623 THI22.00425.009,350.00
431227BS 215/70R15C R623 THI4.00544.002,176.00
541241BS 1200R20 G580 JAP220.002,035.00447,700.00
651284GC 1200R20 AZ0026 CHI80.00895.0071,600.00
761284GC 1200R20 AZ0026 CHI50.001,125.0056,250.00
871285GC 1200R20 AZ0183 CHI40.00925.0037,000.00
981285GC 1200R20 AZ0183 CHI10.001,225.0012,250.00
1091285GC 1200R20 AZ0183 CHI60.001,205.0072,300.00
11101385GC 315/80R22.5 AT161 CHI20.00735.0014,700.00
12111294GC 315/80R22.5 AZ188 CHI20.00745.0014,900.00
13121287GC 315/80R22.5 AZ126 CHI60.00735.0044,100.00
14131305BS 700R16 R230 JAP2.00770.001,540.00
15141306BS 750R16 R230 JAP10.00775.007,750.00
16151306BS 750R16 R230 JAP4.00780.003,120.00
17TOTAL819,846.00
BRANDS
Cell Formulas
RangeFormula
F2:F16F2=D2*E2
F17F17=SUM(F2:F16)




Microsoft Excel ‫‬.xlsx
ABCD
1ITEMCODEBRANDQTY
211221BS 205/70R15C R623 THI20.00
321227BS 215/70R15C R623 THI15.00
431227BS 215/70R15C R623 THI10.00
541241BS 1200R20 G580 JAP10.00
651284GC 1200R20 AZ0026 CHI10.00
761284GC 1200R20 AZ0026 CHI5.00
871285GC 1200R20 AZ0183 CHI5.00
STOCK




before should populate sheets names in listbox1
vf.JPG


after select item from listbox1 should sort data in listbox2
as2.JPG



thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi @abdo meghari.
If I understood you correctly, then try using the following code:
VBA Code:
Option Explicit

Private Sub ListBox1_Click()
    Dim i As Long, j As Long
    ListBox2.Clear

    With Worksheets(ListBox1.Value)

        If .AutoFilterMode Then
            .AutoFilterMode = False
        End If

        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then Exit Sub

        Dim headerArray As Variant
        headerArray = .Range("A1:D1").Value   ' Instead of the range "A1:D1" specify your headers are located

        Dim sortedArray As Variant
        sortedArray = SortArray(.Range("A2:D" & lastRow).Value)    ' Instead of the range "A2:D" specify your range with data

        .Activate
    End With

    Dim numRows     As Long
    numRows = UBound(sortedArray, 1)

    Dim numCols     As Long
    numCols = UBound(sortedArray, 2)

    Dim finalArray() As Variant
    ReDim finalArray(1 To numRows + 1, 1 To numCols)

    For j = 1 To numCols
        finalArray(1, j) = headerArray(1, j)
    Next j

    For i = 1 To numRows

        For j = 1 To numCols
            finalArray(i + 1, j) = sortedArray(i, j)
        Next j

    Next i

    With ListBox2
        .ColumnCount = numCols
        .List = finalArray
    End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ThisWorkbook.Worksheets(1).Activate
End Sub

' Helper function for sorting an array
Private Function SortArray(arr As Variant) As Variant
    Dim i As Long, j As Long, k As Long

    Dim numRows     As Long
    numRows = UBound(arr, 1)

    Dim numCols     As Long
    numCols = UBound(arr, 2)

    Dim sortedArr() As Variant
    ReDim sortedArr(1 To numRows, 1 To numCols)

    For i = LBound(arr, 1) To UBound(arr, 1)

        For j = LBound(arr, 2) To UBound(arr, 2)
            sortedArr(i, j) = arr(i, j)
        Next j

    Next i

    For i = LBound(sortedArr, 1) To numRows - 1

        For j = i + 1 To numRows

            If sortedArr(i, 1) > sortedArr(j, 1) Then

                For k = LBound(sortedArr, 2) To numCols

                    Dim temp As Variant
                    temp = sortedArr(i, k)
                    sortedArr(i, k) = sortedArr(j, k)
                    sortedArr(j, k) = temp
                Next k

            End If

        Next j

    Next i

    SortArray = sortedArr
End Function

Private Sub UserForm_Initialize()
    Dim Sheet       As Worksheet

    For Each Sheet In Worksheets
        ListBox1.AddItem Sheet.Name
    Next

End Sub
Enter this code into your userform. I hope I understood you correctly and helped. Good luck.
sort data in listbox based on selected row from another listbox_v1.png
sort data in listbox based on selected row from another listbox_v2.png
sort data in listbox based on selected row from another listbox_v3.png
 
Last edited:
Upvote 0
thanks
but it's not fast to show data . it will take much time.
I' m not sure if you read this
so I have about 22 sheets and every sheet could contain 5000 rows for each sheet
 
Upvote 0
so I would populate sheets name in listbox1
If you don't have the code to load the data into listbox1, put the following code in your listbox:

VBA Code:
Private Sub UserForm_Activate()
  Dim sh As Worksheet
  For Each sh In Sheets
    ListBox1.AddItem sh.Name
  Next
End Sub



when select item then sort data and show in listbox2
I don't know what you mean by "sort", but I see your example of data in the sheet the same as in the listbox, so you simply have to put the data from the sheet in the listbox
1737224957425.png


If the above is correct, try the following:
VBA Code:
Private Sub ListBox1_Click()
  Dim sh As Worksheet
  Dim i As Long, j As Long, lc As Long, lr As Long
  Dim a As Variant
  
  Set sh = Sheets(ListBox1.List(ListBox1.ListIndex))
  lc = sh.Cells(1, Columns.Count).End(1).Column
  lr = sh.Range("A" & Rows.Count).End(3).Row - 1
  With ListBox2
    .ColumnCount = lc
    a = sh.Range("A1", sh.Cells(lr, lc)).Value
    
    'format numeric values
    For i = 2 To UBound(a)
      For j = 4 To UBound(a, 2)
        a(i, j) = Format(a(i, j), "#,##0.00")
      Next
    Next
    .List = a
  End With
End Sub

If time is too much, then we skip the part of formatting the numbers, and try the following:
VBA Code:
Private Sub ListBox1_Click()
  Dim sh As Worksheet
  Dim i As Long, j As Long, lc As Long, lr As Long
  Dim a As Variant
  
  Set sh = Sheets(ListBox1.List(ListBox1.ListIndex))
  lc = sh.Cells(1, Columns.Count).End(1).Column
  lr = sh.Range("A" & Rows.Count).End(3).Row - 1
  With ListBox2
    .ColumnCount = lc
    .List = sh.Range("A1", sh.Cells(lr, lc)).Value
  End With
End Sub

🤗
 
Upvote 0
Solution
Sorry @abdo meghari, but there is nowhere to get an example file with 22 worksheets and each sheet with 5000 rows of data. I helped as much as I could. Good luck.
 
Upvote 0

If you don't have the code to load the data into listbox1, put the following code in your listbox:

abdo meghari,​

How about this block of code?
Private Sub UserForm_Initialize() Dim Sheet As Worksheet For Each Sheet In Worksheets ListBox1.AddItem Sheet.Name Next End Sub
And how about this?
I would populate sheets name in listbox1 and when select item then sort data and show in listbox2.
You are being vague and not telling the whole story!
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,127
Members
453,641
Latest member
enfkkviesm

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