Wednesday, April 07, 2010

Disable report filter selection in a Pivot Table in Excel

I had to work on disabling a field (report filter) on a Pivot Table in Excel 2007. I googled a lot on this and there was no straight forward answer. Hence thought of sharing my solution with anyone who wants to do the same thing

Here is the code that I have used to achieve this

Right click on the sheet where there is a pivot table and click on View Code
Add the following code

Option Explicit
Private Sub Workbook_Open()DisableSectorSelectionEnd Sub
Sub DisableSectorSelection() Dim pt As PivotTable On Error Resume NextSet pt = Selection.PivotTableIf Not pt Is Nothing Then pt.PivotFields("Sector").EnableItemSelection = False
End Sub

Save and reopen the file.
The filter should not be disbaled on the worksheet.


kalkysister said...

Probably It's past too much time but I need something like your macro, but there's something wrong. It's not work to me!!!!!
Please could you help me?

kalkysister said...

It's not work!! what's wrong?

Rerunning a SSRS subscription report

Yesterday I had a situation where  a couple of SSRS reports failed due to a network upgrade. The SSRS report was not able to access the SQ...