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?

Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of  RevolveScaleR functions -- RxSqlServerData and Rstudio.  These are my learnings as part of ...