Dynamic ListBox for an invoice

Mastermimo

New Member
Joined
Sep 13, 2019
Messages
4
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):
listbox1.jpg


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.
facture.jpg


I am perfectly able to populate the first listbox that gives me the summary of the invoices with this code below :
Code:
[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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.
 
Upvote 0
How about
Code:
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
 
Upvote 0
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.
 
Upvote 0
How about
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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