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?

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...