How to use a ListBox to filter Pivot Tables in Excel 2007

[Peytonput]

Is there a way to use ether a User Form or an ActiveX Listbox/Combo Box to filter out a Pivot Table or All 8 of My Pivot Tables?
I can get my data from my pivot filter into my list box. So say i have a list box full of data! How do i get that data to communicated to my pivottable filter? I want the Listbox to say to the PivotTable, "Hey i want to filter X make it happen!".







I've very new to Excel 2007. I have no way of using slicers in 2010 or beyond. I'm stuck with 2007.

[Richard Buttrey]

Would you confirm that you still only have Excel 2007. If you had 2010 or later you could use Pivot Table Slicers which achieve the same functionality as selecting from a list box and then having a macro to use the selected item in some filtering code.

But perhaps stating the obvious why not just include the field to be filtered in the Report Filter area of the PT and select from there.

[Peytonput]

I'm only limited to 2007. I am aware of splicers in 2010 and later. I have worked with 2010 and later but have never had the need to work with excel 2007.

I could do that with the Report Filter however, this situation requires a list box be added to the sheet.

[Peytonput]

So I took it upon myself to figure it out! So here i go for the next person!

--let make it "Clear" I am using Excel 2007!

You need to fill the Combo box control with all the pivot item of the filter field and a "(All)" item, and then add a change event for that combo box control.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Put these two procedure within the module which the combo box control locate at, run the first procedure "FillComboBox", and then select a item from dropdown list of the combo box control, you would see that the pivottable also would update.


If you need to ask me how to tie in multi pivot tables message me.

[simi_uk]

So I took it upon myself to figure it out! So here i go for the next person!

--let make it "Clear" I am using Excel 2007!

You need to fill the Combo box control with all the pivot item of the filter field and a "(All)" item, and then add a change event for that combo box control.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Put these two procedure within the module which the combo box control locate at, run the first procedure "FillComboBox", and then select a item from dropdown list of the combo box control, you would see that the pivottable also would update.


If you need to ask me how to tie in multi pivot tables message me.

Peytonput, check your inbox.... PM sent regarding the use of multiple pivot tables with your code above.