Needing to operate pivot table with check boxes instead of report filter.

I am creating a dashboard and need to figure out a way to make check boxes operate my pivot table rather than the report filter. I've attached an example spreadsheet to demonstrate my need.

I want the user to only have to option to choose checkbox 1, checkbox 2, or checkbox 3 because normally the report filter contains as many as 100 options (too many for the user to fool with).

So far I've gotten a named range for my checkboxes and another named range for the corresponding checkbox value in the report filter. This is as far as I've gotten. Any help would be much appreciated.

hi jaimeteele, welcome to Excelforum, option, try to check/uncheck boxes

watersev, that is awesome! Did you add anything besides that piece of VBA code?

I really appreciate it .

the code is in module 1 and assigned code to all checkboxes

You saved the day, thanks so much.

Is there a way to restrict the user to only selecting one checkbox at a time?

the easiest way is to change checkboxes to optionbuttons though one optionbuton would always stay checked

That's exactly what I was wanting, I just didn't know it. That's perfect for what I'm doing. Thanks for taking the time to help me. God bless and have a great day.

Ok, I've encountered a problem. I updated the VBA code you created with more detailed info. However, any option button I choose now only returns the value for "Multiple Items" in the report filter field. For instance, if the report filter says "All", and I choose any option button, it returns "Multiple Items". These are the changes I've made to the code...in code. Am I missing something else, like a detail not in the VBA code?


Private Sub test()

Set sh = ActiveSheet

On Error Resume Next
Application.ScreenUpdating = 0

With Sheets("Detailed Info").PivotTables(1).PivotFields("WBS Element")

.ClearAllFilters

.PivotItems("Awesome_HPAJ").Visible = sh.OptionButtons(1).Value = 1
.PivotItems("Cool_HPAJ").Visible = sh.OptionButtons(2).Value = 1
.PivotItems("Excellent_HPAJ").Visible = sh.OptionButtons(3).Value = 1
.PivotItems("High Five_HPAJ").Visible = sh.OptionButtons(4).Value = 1
.PivotItems("Amazing_HPAJ").Visible = sh.OptionButtons(5).Value = 1
.PivotItems("Right On_HPAJ").Visible = sh.OptionButtons(6).Value = 1
.PivotItems("Wow_HPAJ").Visible = sh.OptionButtons(7).Value = 1



End With

Application.ScreenUpdating = 1

End Sub