EXCEL/VBA: Filteren d.m.v. listboxen

[Excellerend]

Beste VBA-specialisten,

Ik ben druk doende in Excel om ons huidige rapportagesysteem te verbeteren. Één onderdeel daarvan is "automatisch" genereren van een opdrachtbon.
Ik heb ter verduidelijking een vereenvoudigd voorbeeldbestand toegevoegd.

Het idee is dat je op (1) projectnummer, (2) weeknummer en (3) bedrijfsnaam kan filteren en dat de gefilterde resultaten naar een opdrachtbon format (ander tabblad) worden gekopieerd.
De gefilterde resultaten zijn er in de 'echte' sheet veel meer (b.v. kolommen als 'aantal', 'prijs per stuk' en 'totaalprijs' etc.)

Ik dacht het samenstellen van een opdrachtbon door middel van listboxen te doen.

Stap 1 is het verzamelen van unieke waarde om de listboxen te vullen, dat is gelukt. (mede dankzij een draadje op dit forum)
Stap 2 is het kiezen van een (1) een project, (2) een week en (3) een bedrijf om vervolgens een opdrachtbon te maken. Hier gaat het echter fout. Zodra het eerste criteria is gekozen (projectnummer) zijn in mijn voorbeeld alle weken en bedrijven nog te selecteren, echter door de keuze van het projectnummer worden een aantal weken en/of bedrijven al uitgesloten. De vraag is dus; hoe kan ik de inhoud van listbox 2 (weeknummer) laten refreshen nadat er een project is gekozen én vervolgens moet ook listbox 3 (bedrijfsnaam) refreshen na het kiezen van een weeknummer.

De gekozen waarden van de listboxen 1, 2 en 3 dienen als input voor de filter, maar als de combinatie van gekozen waarden niet mogelijk is filtert Excel niet (logisch) en komen er dus waarden op de opdrachtbon te staan die er niet op horen.

Een voorbeeld (zie voorbeeldbestand) te verduidelijking:
1. Klik op de knop '1. Listboxen'.
2. Klik in listbox 1 op (project) 110
3. Project 110 komt voor in week 40 en 41, dat klopt, klik in listbox 2 (week) op 41
4. In listbox 3 zijn nu alle 6 de bedrijven nog een mogelijkheid. Dat is dus fout, conform de brongegevens is enkel de bedrijf ABC een mogelijkheid.
(n.b. Door te dubbelklikken op het bedrijf naar keuze wordt het Userform gesloten en de gemaakte keuze weggeschreven naar de Range G3:I3)

Indien er betere oplossingen zijn, ik laat me graag verrassen, alvast bedankt voor jullie hulp!

Gr,
Peter

Bijlage:
Bekijk bijlage Voorbeeld_Listboxen.xlsm

 

[HSV]

Zet deze twee codes eens bij in het form.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
sn = Sheets(1).Cells(1).CurrentRegion.Offset(2).Resize(, 3).SpecialCells(2)
For i = 1 To UBound(sn)
 If sn(i, 1) = ListBox1.Value Then
    If InStr(sq, sn(i, 2) & "|") = 0 Then sq = sq & sn(i, 2) & "|"
  End If
 Next i
ListBox2.List = Split(sq, "|")
End Sub
'===============================================

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
sn = Sheets(1).Cells(1).CurrentRegion.Offset(2).Resize(, 3).SpecialCells(2)
For i = 1 To UBound(sn)
 If CStr(sn(i, 2)) = ListBox2.Value Then
    If InStr(sq, sn(i, 3) & "|") = 0 Then sq = sq & sn(i, 3) & "|"
  End If
 Next i
ListBox3.List = Split(sq, "|")
End Sub

 

[Excellerend]

Bedankt voor je reactie HSV, en je hebt ook bijna de oplossing te pakken!

1. Als je een keuze maakt in Listbox 1 wordt inderdaad de gefilterde resultaten in Listbox 2 weergegeven. So far so good.
2. Als je een keuze maakt in Listbox 2 wordt ook in Listbox 3 gefilterde resultaten weergegeven, maar dat zijn resultaten die enkel zijn gefilterd op basis van de gekozen waarden in Listbox 2.

Graag zou ik in Listbox 3 de gefilterde resultaten zien op basis van de gekozen waarden in Listbox 1 én Listbox 2.

En aangezien ik geen ene bliksem van je code snap, geloof ik ook niet dat ik het zelf voor elkaar ga krijgen
Zou jij nog een poging willen wagen?

Bedankt vast,
Peter

 

[HSV]

Zeker.

Verander eens in de listbox2 code.

If CStr(sn(i, 2)) = ListBox2.Value Then

in.

If sn(i, 1) = ListBox1.List(ListBox1.ListIndex, 0) And CStr(sn(i, 2)) = ListBox2.Value Then

 

[Excellerend]

Yes, het is me uiteindelijk gelukt!

De onderstaande regel maakt is het voorbeeldbestand netjes de selectie van de gebruikte Range.

sn = Sheets(1).Cells(1).CurrentRegion.Offset(2).Resize(, 3).SpecialCells(2)

Echter is de 'echte' sheet werd enkel de eerste regel van de gebruikte Range geselecteerd, en voordat ik daar achter was...

Bedankt voor de onmisbare hulp.

Voortbordurend op de mogelijkheden van de Listboxen, je kan je Listbox optioneel meerdere waarden laten selecteren. (MultiSelect)
Is het mogelijk om de brongegeven op basis van meerdere geselecteerde waarde te laten filteren?

 

[HSV]

Test het maar eens Peter.

 

[Excellerend]

Gedaan! En werkt perfect. Nu nog even overgieten naar een mooie layout en dan gaan we er een hoop plezier aan beleven.

Nogmaals, BEDANKT!