Pivot Table - Restrict All

[Chunkus]

Hi

Been using Excel for years, with ok VBA skills. Thought this would be easy.......

I have a pivot table, where I want to prevent the use from being able to select all items.

E.g Data
Salesman: Tom, Dick & Harry
Sales: 2, 3, 1

Within the pivot table, I do want users to be able to select '(all)', and see the total sales of 6. I only want them to select: Tom, Dick or Harry and see sales of: 2, 3 or 1. Ideally, I would like the '(all)' item to be hidden, but disabled would be better than nothing.

The data source on NO OLAP, ie basic Excel data.

As I said, I thought this would be easy!!!!! Excel does not normally beat me, but this is :-(

Can anyone help, this is driving me mad.

 

[Chunkus]

Hi

Created a workaround.

Separate drop down (with just: Tom, Dick & Harry).
I then use the selected value from this drop down to change the page field of the pivot table. (I 've hidden the page field, and protected the worksheet to prevent it being unhidden)

ActiveSheet.Unprotect Password:="Blah"
ActiveSheet.PivotTables("PivotTable1").PivotFields("MyField").CurrentPage = _
Range("Selected").Value
ActiveSheet.Protect Password:="Blah"

I prefer real solution, instead of workarounds, but when needs must!.....

Seems to works ok.

Hoep this is useful to someone ;-)

 

[SkipVought]

What you posted IS a real solution.

In the future, consider posting VBA questions in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]