Listbox for pivot table selection

[jomili]

I have a macro that selects specific items in the various pagefields of a pivot table, but one pagefield is throwing me, because I don't have a "Specific" item to select.

The overall purpose is to create a forecast, and to do that we have to select some of our history, but not all. Therefore, when it comes to this particular pagefield, the user needs to select from 3 to 12 different months to use, depending on the user's need.

I would like to use a listbox, and have it populate from the Pagefield, so the user can select the dates to use from that, but I don't know how to set it up. I've looked on the internet, and found a lot of hits but no help. I'd sure appreciate some help from you.

Thanks,
John

[vikas.bhandari]

Please use the following code. It will add the items. Let me know if you nee something else too.

Thanks,
Vikas

Assumptions : Sheet name if Sheet2, and pivot table name is PivotTable 1

Please Login or Register  to view this content.

[jomili]

Vikas,

I'm getting a "Variable not Defined" on ComboBox1. Do I need to first create a blank comboBox?

[vikas.bhandari]

Oh yea, Combobox1 is the control which I am loading the pagefields.

Vikas

[jomili]

Vikas,

I don't understand your answer. Let me see if I can make my situation clearer:
User opens "Staffing Projection Tool.xls", which opens "Paid FTEs.xls", which contains the pivot table in question. Userform comes up, asks user which Region they want. User selects, for instance, region 030. Macro selects region 3 in pivot table, makes some other modifications to pivot, then it needs to know what dates to use. I don't want my user interacting with the spreadsheet, but the user needs to select the dates to use. That was why I asked for a listbox. If we can do it with a combobox that's just fine, I just don't understand how. Below is the code I'm trying to use.

Please Login or Register  to view this content.

[vikas.bhandari]

Hi there!

It is upto you whether you want to use Listbox or Combobox. You can use and chose the data in any of the control of your choice. Listbox will allow user to select multiple items, but combobox gives option to select only one. So this is entirely your choice.

If you use Combobox, then you will need to load the values in Combobox when you initialize the form so that user can select the date before you click the button to generate report.

I will do something like following code to ensure that the date is selected in pivot table. If it doesn't solve your query, then please send the spreadsheet either by uploading here, or if it is heavy then you can send it @ [email protected].

Please Login or Register  to view this content.

Waiting for your response!!!

Thanks,
Vikas

[jomili]

Vikas,

I've attached a sample, but had to WAY strip down the data file, so I don't guarantee it will work flawlessly. The way it's supposed to work, open the Staffing Projection Tool. Then it normally will open the PAID_FTES_BR20102.xls (also in the zip file), but since it won't know the file path you might have to help with that. Once it gets PAID FTEs open, it will make adjustments to the pivot table. It will throw up a dialogue to choose the region (choose region 3; that's the only data I shipped). Then I want another dialogue where the user can select the dates ( multiple dates, from 3 to 15 dates) to be used in the forecaster. That's where it's breaking down. Any help you can provide is greatly appreciated. I started, based on the code you provided yesterday, to build another userform, but it's not completed.