populate userform listbox with pivotRowField items

[ejvenema]

Hi there,

I'm an average vba programmer (actualy a mathteacher and an exam manager at a k12 (kind off) school in the Netherlands).

And I'm realy stuck within vba with the following: I try to populate an userform listbox with pivotrow.pivotitems of a filtered pivottable. And I'm searching the web for three days now and can't find a solutiond.

Situation: I have an Excel workbook with several worksheets. A main sheet (kind of a daschboard where some selections can be made), a hidden lookup sheet and a student sheet (an excel table with student info). On the student sheet, students are assigned to courses and within are assigned to groups (because exam groups cant's be to big). On the hidden lookup sheet I defined a pivot table that shows the groupnames and the assinged studentnames depending on the selected course (from the main sheet). So far so good. Populating a listbox isn't a problem, but to retreve an array with groupnames from the pivotrows and an array with assigned studentnames from the pivottable is a problem...

Could anyone help me out with this?

Kind regards,
Eric-Jan Venema

[snb]

Sure, but hardly without a sample workbook.


PS. waarom plaats je je vraag niet in Helpmij.nl in het VBA subforum ?

[ejvenema]

Thanks snb!
I'm working in the 'real'-thing. Due to student privacy issues I hasitated to add the Excel workbook. I made some screen dumps (but honestly I don't know how to add those pictures in this thread), would that help? Or still add an sample workbook?

PS. Dank je wel voor de tip. Ga ik het daar ook posten...

[snb]

Only a sample file containing the essentials of your question (and certainly no personal information !!) will do. (we don't like pictures).
It's easier for you do indicate in the file what result you are expecting and based on what.

PS. Als je dat doet maak dan svp een link naar deze draad, anders word je beschuldigd van 'crossposting'.

[ejvenema]

Thnx again snb!
I'm quit a newbee in a forumcommunity, so thanks for warning about crossposting...
Attached the Excel file I'm working on (without any personal stuff and removed sheet security passwords).

On the first PV sheet, colleagues can select a course (vak). Based on that selection a pre-difined set of exam-parts will apear (and matching time register sheets) and through a click on the button [Kies kandidaten] they get the possibility (in a userform) to select from course assigned groups of students. The meaning of this all is that the group of choise puts all the names in the PV sheet candidate list, so my colleagues don't have to find out who the assigned students are end don't have to type there names.

In Private Sub UserForm_Initialize() [frmKandidaten], I try to use the rowfield items from the defined pivottable on the lookup sheet [opzoek] to populate the userform listbox [lstGroepen]. But I don't get it. I don't know enough about the pivottable object properties and methods to retrieve the pagefiltered pivotrows and assign them to an array so I can use that in the .additem for the listbox... There is in the vba code of the userform a debug-stop-row for a breakpoint on [piet=2] so I can symply observe the locale variabels...

After this is solved, I have to write an eventhandler for the listbox-click (select the group of choise...) and so on...

Already very grateful and with kind regards, Eric-Jan