Radio buttons do not return correct data from pivot table.

[jaimeteele]

I have a pivot table containing the following data elements:

EAST.01.07.01
NORTH_HPAJ
SOUTH_HPAJ
WEST.01.06.1
WEST.01.06.1.2
WEST.01.09.1.2

I have 4 option buttons labeled NORTH, SOUTH, EAST, and WEST. As you can see, WEST is comprised of 3 elements, but all these elements are not useful to the user. Only WEST.01.06.1 should be seen by the user. My VBA code is not written to properly return the data. When I choose any of the option buttons, they all return the excess data from WEST. This is just a very simplified explanation, because in reality there will be dozens of unused elements that I don't want to show up. I've attached the relevant spreadsheet. Also, here is my code. I appreciate any help. Thanks and have a blessed day.


Private Sub test()

Set sh = ActiveSheet

On Error Resume Next
Application.ScreenUpdating = 0

With Sheets("DETAILS").PivotTables(1).PivotFields("Element")

.ClearAllFilters

.PivotItems("NORTH_HPAJ").Visible = sh.OptionButtons(1).Value = 1
.PivotItems("SOUTH_HPAJ").Visible = sh.OptionButtons(2).Value = 1
.PivotItems("WEST.01.06.1").Visible = sh.OptionButtons(4).Value = 1
.PivotItems("EAST.01.07.01").Visible = sh.OptionButtons(3).Value = 1


End With

Application.ScreenUpdating = 1

End Sub

[Norie]

Why should only that one item be returned for WEST?

This will return all items based on which option button has been selected.

Please Login or Register  to view this content.

[Andy Pope]

Please Login or Register  to view this content.

Although I think you might be better off creating a new field for Areas N/S/E/W and filtering the report by those.

[jaimeteele]

Only 1 item should be returned for WEST because the other items are either confidential or old and no longer relevant. These items cannot be removed from the data, however, because they are used in other parts of the report. Showing all items is not an option.

[Norie]

What indicates the items that are confidential/old/irrelevant?

[jaimeteele]

The text/numbers that come after the WEST/EAST/NORTH/SOUTH. Otherwise nothing indicates it other than just the fact that I know which ones should be seen and which ones shouldn't.

[jaimeteele]

Thanks for your quick response. The code you attached is still including the unneeded data. Is there not a way to tell it the ones I want to see and make it show only those?

[Norie]

You'll need to write the code so it excludes the elements you don't want.

Since there's no criteria for that I can't see any other way then hard-coding what not to display

[jaimeteele]

Norie, I could probably make your code work for me; however, it supercrunches my computer when I put it into my real file and it shuts Excel down. Is there a way to fix this?

[Andy Pope]

Please Login or Register  to view this content.

I still think you should add another data field that contain information for those records you want displayed.