Userform listbox vullen met rij-items van een gefilterde draaitabel

[ejvenema]

Hallo allemaal,

Deze vraag heb ik ook gepost in VBA Express forum (zie thread: http://www.vbaexpress.com/forum/showthread.php?58753-populate-userform-listbox-with-pivotRowField-items&p=357599#post357599)

In de bijlage de Excel file waar het om gaat (een geautomatiseerd Procesverbaal voor praktijk examens).
Bekijk bijlage PVtest.xlsm

Op het tabblad PV, selecteren collega's (hun) praktijkvak. Op basis van deze keuze wordt een voorgedefinieerde lijst van praktijkexamen onderdelen opgehaald met dito tabbladen voor tijdregistratie. Door klik op de knop [Kies kandidaten] wordt er een userform getoond waar collega's de mogelijkheid hebben om een groep kandidaten te kiezen die toegewezen zijn aan het praktijkvak en een examengroep (groepen mogen niet te groot zijn, vandaar de onderverdeling in kleinere groepen). Dit is een service naar m'n collega's, zodat ze niet hoeven uit te zoeken welke leerlingen in een groep zitten en ze deze ook niet hoeven over te typen.

In Private Sub UserForm_Initialize() van het formulier [frmKandidaten], wil ik de draaitabel rijen van de draaitabel op het tabblad opzoek, te gebruiken om de listbox [lstGroepen] te vullen. Ik heb al drie dagen op het web gezocht naar een oplossing, maar krijg het niet voorelkaar. Ik weet onvoldoende van de objectstructuur van een draaitabel om deze gegevens in een array te plaatsen om vervolgens deze te gebruiken om de listbox te vullen.
In de userform code heb ik een debug-stop-regel toegevoegd waarop ik een onderbrekingspunt kan zetten, zodat ik de lokale variabelen makkelijk kan bekijken.

Als dit opgelost is, dan komt de volgende stap om een eventhandler te schrijven op de groep-listbox keuze, zodat de bijhorende leerlingen in de listbox voor kandidaten komt, waarna deze in het PV tabblad overgenomen kan worden (en zo verder...)

Is er iemand die me hiermee verder kan helpen?

Alvast dank en groeten, Eric-Jan Venema

 

[snb]

Dat gaat zo:

Private Sub UserForm_Initialize()
    Sheets("opzoek").PivotTables(1).PivotSelect "groepnaam", 1
    Selection.ShowDetail = True
    lstgroepen.List = Selection.SpecialCells(2).value
    Selection.ShowDetail = True
End Sub

 

[ejvenema]

Dank je wel snb,

In m'n zoektocht ben ik wel ergens langs PivotSelect gekomen, maar begreep dat dat alleen kon als alles zichtbaar is. Sheet [opzoek] wil ik nu juist verborgen houden omdat het voor collega's alleen maar onnodig afleid.

... maar het werkt helaas niet
Ik krijg een foutmelding: "Fout 1004... Eigenschap ShowDetail van klasse Range kan niet worden ingesteld"
Als ik de regels met ...ShowDetail... 'uitzet, dan krijg ik de UserForm met in de listbox de inhoud van cel A1 op sheet [PV] zien "Procesverbaal CSPE".

Het lijkt alsof ik nog een Range-variabele moet declareren? Deze dan omzetten met .value naar een array?
Verder weet ik niet precies wat ...SpecialCells(2)... doet (uit de selection alleen cellen met waarden ophalen?)

Gr. EJ

 

[snb]

Toch wel met:

Private Sub UserForm_Initialize()
  Sheets("opzoek").PivotTables("pvtGroepen").PivotSelect "groepnaam", 1
  Selection.ShowDetail = False
  lstGroepen.List = Selection.SpecialCells(2).Value
  Selection.SpecialCells(2).ShowDetail = True
End Sub

PS. aarzel niet de F1 toets in de VBEditor te gebruiken nadat je een term in de code hebt geselecteerd (bijv. specialcells)

Mij lijkt de annuleren-knop overbodig. Het userform bevat al een sluitkruis in de rechterbovenhoek.

 

[ejvenema]

Dank je wel snb weer voor snelle reactie.

Hmmmm... je code neem ik exact over en krijg nog steeds genoemde foutmelding.
Zowel in het originele bestand als in het bestand dat ik als test meestuurde. Ook geprobeerd op twee verschillende PC's (werk met windows 7 en Excel 2016 en privé windows 10 en Excel 2016)

Zou het een Excel (of vba) instelling kunnen zijn?

... inderdaad F1 helps (sorry).

 

[ejvenema]

... het leek me zo eenvoudig om een draaitabel te maken en daaruit de benodigde gegevens te halen.

Misschien is dat toch niet de juiste oplossing voor de functionaliteit die ik wil aanbieden.
Wellicht is het sneller en handiger om rechtstreeks de tabelKandidaten op sheet [kandidaten] binnen vba te filteren op het veld

 en dan data van het veld [GroepNaam] aan een array toe te wijzen...

 

[snb]

Het is mij niet duidelijk welke gegevens je precies uit de draaitabel wil halen.

Mijn code loopt vlekkelings in jouw bestand: zie bijlage.


Als je de volledige tabel van unieke GroepNaam wil hebben is een andere methode geschikter.

 

[ejvenema]

Als ik jouw bestand gebruik, krijg ik dezelfde foutmelding als ik je al verteld heb...
Dus ik snap er niks meer van. Toch even een afbeelding (sorry).


Uit de draaitabel heb ik inderdaad (eerst) de unieke GroepNaam van het betreffende vak nodig. Dan in de listbox [lstKandidaten], initieel alle kandidaten van dat vak, waarna bij een keuze uit de groepen de bijhorende kandidaten van die groep in lstKandidaten wordt getoond.

Ben wel nieuwsgiering naar je geschiktere methode...

Thnx again.

 

[snb]

Het je het door mij geplaatste bestand ongewijzigd laten lopen ?

Doorloop de code met F8 en toon een printscreen van de code met de geel gemarkeerde regel.

 

[ejvenema]

Ja, ik heb jouw bestand gebruikt en laten lopen.
Als het handig is, maak ik er wel even een kort filmpje van...

 

[snb]

Zet achter

Private Userform_initialize()
stop
.....

Laat de macro lopen en maak een schermafdruk wanneer de fout wordt gegenereerd.(jouw signaal is nl. niet duidelijk).
Met filmpjes doe ik niets.

 

[ejvenema]

Als ik na "deze" regel op F8 druk, krijg ik onderstaande foutmelding.

 

[snb]

Het is niet de code die ik in mijn bestand heb staan,

 

[ejvenema]

Da's vreemd. Opnieuw heb ik je bestand opgeslagen. Alles afgesloten en alleen jouw bestand gedraaid.
Excel wordt opgestart. Het stopt keurig bij stop. Daarna met F8 er doorheen. Opnieuw dezelfde foutmelding...
Ik verander er niets aan. Nu zie ik wel een verschil met mijn eerste schermafdruk, dat in jouw regel na de stop, achteraan geen , 1 meer staat

 

[snb]

Welke Excelverstie gebruik je ?

 

[ejvenema]

Excel 2016 (zie bericht van 15:02)

 

[snb]

Ik hoop dat je niets in het werkblad 'opzoek' in de weergave van de draaital hebt gewijzigd voordat je de macro liet lopen.
De code is nl. situatie afhankelijk: als alle items in de 1e kolom van de draaitabel zijn uitgeklapt kan ie ingeklapt worden, anders ontstaat een foutmeldikng.

 

[ejvenema]

Nee, niets gewijzigd! Direct gegaan naar sheet [PV] en toen op de knop [Kies kandidaten] geklikt. Is er wellicht een verbinding met iets dat niet in de standaard VBA-bibliotheek zit?

Krijg de indruk dat de methode via een draaitabel dan toch te onstabiel is :-(
Ben nu je tutorial vba voor smarties: scripting.dictionary aan het bestuderen. Dat lijkt een geschiktere en snellere methode te zijn…

 

[snb]

Mij lijkt de draaitabel inderdaad overbodig als die uitsluitend voor het vullen van het userform gebruikt wordt.

Dan volstaat deze code

Dim sn

Private Sub UserForm_Initialize()
    sn = Sheets("kandidaten").Cells(1).CurrentRegion
    
    vak.List = Sheets("opzoek").Range("I5:J21").Value
End Sub

Private Sub vak_Change()
    For j = 2 To UBound(sn)
      If sn(j, 3) = vak.Column(1) Then
         c00 = c00 & "_" & sn(j, 1)
         c01 = Replace(c01, "_" & sn(j, 6), "") & "_" & sn(j, 6)
       End If
    Next
    
    lstKandidaten.List = Split(Mid(c00, 2), "_")
    lstGroepen.List = Split(Mid(c01, 2), "_")
End Sub

Private Sub lstGroepen_Change()
    For j = 2 To UBound(sn)
      If sn(j, 6) = lstGroepen.Value Then
         c00 = c00 & "_" & sn(j, 1)
       End If
    Next
    lstKandidaten.List = Split(Mid(c00, 2), "_")
End Sub

 

[ejvenema]

snb, dit is wat het moet zijn (op een paar kleine tweaks na)! Top! dank je wel

De draaitabel had ik in eerste instantie gebruikt om snel een overzicht te hebben van de leerlingen in de verschillende groepen. In m'n naïviteit veronderstelde ik dat het dan super eenvoudig was om deze gegevens daar uit te lezen en in een userform te gebruiken. Maar helaas.
Wat mij betreft is dit opgelost. Ik heb super veel gehad aan je aanwijzingen e.d.

VBA voor smarties is trouwens top!
Groet en Thnx !!!
Eric-Jan Venema