I need procedure to populate data in listbox based on another listbox

[KalilMe]

Hi,
when select sheet name from combobox5 will populate data in listbox1.
what I want to show data in listbox2 based on listbox1. should brings data from listbox1 and show in listbox2 item ,name and invoice no and TOTAL . just brings amount from TOTAL row for each name and invoice no and exclude the others columns and insert TOTAL to sum TOTAL column .

Dropbox

 

[DanteAmor]

To sort the data in listbox2, I am temporarily using columns AA to AD from sheet 1.
In the end the data in those columns is deleted.
If the columns are not available, you can use other columns that are available in your sheet.

Replace all your code for this:

Option Explicit 'At the beginning of all the code

Dim a As Variant, FirstCB5 As Boolean

Private Sub ComboBox3_Change()
Call FilterData
End Sub

Private Sub ComboBox4_Change()
 Call FilterData
End Sub

Private Sub ComboBox5_Change()
  ' see if this is the first UF activation (where FirstCB isn't set)
  If ComboBox5.Value <> "" And FirstCB5 = False Then
    FirstCB5 = True
    Exit Sub
    ' if not, refresh UF for selected sheet
  Else
    Call UserForm_Activate
    Call FilterData
  End If
End Sub

Private Sub ComboBox1_Change()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  Call FilterData
End Sub

Private Sub FilterData()
  Dim i As Long, j As Long, k As Long
  Dim cb1 As String, cb2 As String, cb3 As String, cb4 As String
  Dim dic As Object
  Dim ky As String, itm As Variant
  Dim ws As Worksheet
  Dim total As Double

  Set ws = Sheets(1)
  Set dic = CreateObject("Scripting.Dictionary")
    
  ListBox1.Clear
  ListBox2.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  ReDim c(1 To UBound(a, 1), 1 To 4)
  
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cb1 = a(i, 2) Else cb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cb2 = a(i, 3) Else cb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cb3 = a(i, 4) Else cb3 = ComboBox3.Value
    If ComboBox4.Value = "" Then cb4 = a(i, 6) Else cb4 = ComboBox4.Value
    If LCase(a(i, 2)) Like LCase(cb1) & "*" And LCase(a(i, 3)) Like LCase(cb2) & "*" And LCase(a(i, 4)) Like LCase(cb3) & "*" And LCase(a(i, 6)) Like LCase(cb4) & "*" Then
      k = k + 1
      For j = 1 To 7
        a(i, 5) = Format(a(i, 5), "#,##0.00")
        a(i, 6) = Format(a(i, 6), "#,##0.00")
        a(i, 7) = Format(a(i, 7), "#,##0.00")
        b(k, j) = a(i, j)
      Next
    
      If a(i, 2) <> "" And i > 1 Then
        ky = a(i, 2) & "|" & a(i, 3)
        dic(ky) = CDbl(dic(ky)) + a(i, 7)
      End If
    End If
  Next
  
  If k = 0 Then
    MsgBox "doesn't show data"
  Else
    ListBox1.List = b
    
    Application.ScreenUpdating = False
    ws.Range("AA:AD").ClearContents
    ws.Range("AA:AD").NumberFormat = "@"
    ws.Range("AA1").Resize(1, 4).Value = Array("ITEM", "CUSTOMER", "INVOICE NO", "TOTAL")
    i = 2
    For Each itm In dic.keys
      ws.Range("AA" & i).Value = i - 1
      ws.Range("AB" & i).Value = Split(itm, "|")(0)
      ws.Range("AC" & i).Value = Split(itm, "|")(1)
      ws.Range("AD" & i).Value = Format(dic(itm), "#,##0.00")
      total = total + dic(itm)
      i = i + 1
    Next
    ws.Range("AA1:AD" & i).Sort ws.Range("AA1"), xlAscending, ws.Range("AB1"), , xlAscending, Header:=xlYes
    ws.Range("AA" & i).Value = "TOTAL"
    ws.Range("Ad" & i).Value = Format(total, "#,##0.00")
    ListBox2.List = ws.Range("AA1:AD" & i).Value
    ws.Range("AA:AD").ClearContents
    Application.ScreenUpdating = False
  End If
End Sub

Private Sub UserForm_Activate()
  Dim dic1 As Object, dic2 As Object, dic3 As Object, dic4 As Object
  Dim i As Long
  Dim ws As Worksheet
  ' check if CB5 is set
  If ComboBox5.Value <> "" Then
    Set ws = Sheets(ComboBox5.Value)
  Else
    Set ws = Sheets(1)
    ' show first sheet in CB5 (will trigger ComboBox5_Change)
    ComboBox5.Value = ws.Name
    ' set variable
    FirstCB5 = True
  End If
  
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  Set dic4 = CreateObject("Scripting.Dictionary")
  
  'Load info from sheet SH into a variable, named a
  a = ws.Range("A1:G" & ws.Range("G" & Rows.Count).End(3).Row).Value
  'Set column widths of the Listbox
  With ListBox1
    .RowSource = ""
    .ColumnWidths = "60;80;80;80;40;80;30"
    .ColumnCount = 7
    .Font.Size = 10
    .Clear
  End With
  
  'Add the values of the range (a) to the various
  For i = 2 To UBound(a, 1)
    If a(i, 2) <> "" Then dic1(a(i, 2)) = Empty
    If a(i, 3) <> "" Then dic2(a(i, 3)) = Empty
    If a(i, 4) <> "" Then dic3(a(i, 4)) = Empty
    If a(i, 6) <> "" Then dic4(a(i, 6)) = Empty
  Next
  
  ' clear CB dropdowns
  For i = 1 To 4
    Controls("ComboBox" & i).Clear
  Next
  
  'Put the dictionaries/lists as source of the Comboboxes
  ComboBox1.List = dic1.keys
  ComboBox2.List = dic2.keys
  ComboBox3.List = dic3.keys
  ComboBox4.List = dic4.keys
End Sub

 

[KalilMe]

awesome!
thank you for this work.