Dynamic ListBox for an invoice

[Mastermimo]

Hello Everybody,

I woud lile your help on some issues i'm facing when i try to populate a combobox from a specific range based on a certain condition. Below is a listbox i created that provides the summary of all the invoices registered (see image below as an example):

When i select an i select an invoice from the list above, i would like to see in another listbox as seen below the list of all the items in that invoice, per example invoice 1234 (but only 1234) and not the rest of the invoices.

I am perfectly able to populate the first listbox that gives me the summary of the invoices with this code below :

[COLOR=#0000FF]Sub[/COLOR][COLOR=#333333] populatelstSales[/COLOR][COLOR=#333333]([/COLOR][COLOR=#333333])[/COLOR] 
[COLOR=#0000FF]Dim[/COLOR] ws      [COLOR=#0000FF]As[/COLOR] Worksheet
[COLOR=#0000FF]Dim[/COLOR] rng     [COLOR=#0000FF]As[/COLOR] Range
[COLOR=#0000FF]Dim[/COLOR] MyArray                 
 
[COLOR=#0000FF]Set[/COLOR] ws = Sheets([COLOR=#FF0000]"SalesRawData"[/COLOR])
 
[COLOR=#0000FF]Set[/COLOR] rng = ws.Range([COLOR=#FF0000]"B3:R"[/COLOR] & ws.Range([COLOR=#FF0000]"B"[/COLOR] & ws.Rows.Count).End(xlUp).Row)
 
    [COLOR=#0000FF]With[/COLOR] Sheets([COLOR=#FF0000]"SALES"[/COLOR]).lstSales
        .Clear
        .ColumnHeads = [COLOR=#0000FF]False[/COLOR]
        .ColumnCount = rng.Columns.Count
 
        [COLOR=#808080]'~~> create a one based 2-dim datafield array[/COLOR]
         MyArray = rng
 
        [COLOR=#808080]'~~> fill listbox with array values[/COLOR]
        .List = MyArray
 
        [COLOR=#808080]'~~> Set the widths of the column here. Ex: For 5 Columns[/COLOR]
        [COLOR=#808080]'~~> Change as Applicable[/COLOR]
        .ColumnWidths = [COLOR=#FF0000]"100;100;100;100;100;100;150;"[/COLOR]
        .TopIndex = [COLOR=#CC66CC]0[/COLOR]
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]With[/COLOR]
 
    Range([COLOR=#FF0000]"A1"[/COLOR]).Select
  [COLOR=#0000FF]End[/COLOR][COLOR=#333333] [/COLOR][COLOR=#0000FF]Sub[/COLOR]

If anyone can please help me on how to do what i'm trying to do, such as populate the second list box based on the selection of the first one.

Thank you for your help in advance.

Amo.

 

[Akuini]

It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.

 

[Fluff]

How about

Private Sub LstSales_Click()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   With Sheets("SalesRawData")
      Ary = .Range("B3:R" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
      r = Application.CountIf(.Range("B:B"), Me.LstSales.Value)
   End With
   
   ReDim Nary(1 To r, 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary)
      If Ary(r, 1) = Me.LstSales.Value Then
         nr = nr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(nr, c) = Ary(r, c)
         Next c
      End If
   Next r
   With Me.[COLOR=#ff0000]ListBox1[/COLOR]
      .Clear
      .ColumnCount = UBound(Nary)
      .List = Nary
   End With
End Sub

This needs to go in the Sales sheet module, change value in red to suit

 

[Mastermimo]

Thank you so much Fluff. I've been struggling forever on this one, you are a savior

 

[Mastermimo]

It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.

Sorry i dont know what exactly you meant, im very new here. Can i upload my file directly here next time.

 

[Fluff]

Thank you so much Fluff. I've been struggling forever on this one, you are a savior

Glad to help & thanks for the feedback

 

[Akuini]

Sorry i dont know what exactly you meant, im very new here. Can i upload my file directly here next time.

No, you can't upload your file directly here.

 

[Mastermimo]

Hi Fluff,

i'm trying to reuse your code as per the file i added below but still have an error i dont know how to resolve. As you can see in the file, i populate the first lstbox in the INVOICE LIST tab from the data provided in sheet1 (which is basically a pivot table with data from PurchaseRawData). To populate that first listbox, i run the code populatelstPO. So when i click on the first invoice 1234, after using the code you shared with me, i want to be able to see all the 1234 invoices in the second listbox (INVOICE ITEMS). Currently i'm having an error and i dont know whats the issue.

https://drive.google.com/file/d/1zPfwpKhs13Gqj4TAQc8aTO6Tga1cSB_H/view?usp=sharing

i've been struggling for days now and i have to turn to you guys for help. Thanks again.

 

[Fluff]

How about

Private Sub lstInvoices_Click()
    Dim Ary As Variant, Nary As Variant
    Dim r As Long, c As Long, nr As Long
    
    With Sheets("PurchaseRawData")
       Ary = .Range("B3:R" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
       r = Application.CountIf(.Range("C:C"), Me.lstInvoices.Value)
    End With
    
    ReDim Nary(1 To r, 1 To UBound(Ary, 2))
    For r = 1 To UBound(Ary)
       If Ary(r, 2) = Me.lstInvoices.Value Then
          nr = nr + 1
          For c = 1 To UBound(Ary, 2)
             Nary(nr, c) = Ary(r, c)
          Next c
       End If
    Next r
    With Sheets("INVOICE ITEMS").lstInvoiceItems
       .Clear
       .ColumnCount = UBound(Nary)
       .List = Nary
    End With
End Sub