Excel 2007 : Pivot table

**guy_tayeb** · 07-06-2009, 05:12 AM

Hi,

I need to protect one of the filters with password that it wont be able to change the chosing thing in that cell and giv an option to change all the other filters.

in the pivot table in the report filters area i have 2 filters and i want to be able to protect one and keep it as it is and not to be able to change the filter, and for the other filter keep it open and changeable.

hope i was clear enough and you will be able to help me.

Thanks,
Guy

**DonkeyOte** · 07-06-2009, 06:25 AM

Guy, one option would be to use VBA and use the PivotTable update event to reset the filter whenever the PT updates thereby ensuring it is always set to be the correct option ... obviously there are two major caveats to this:

1 - VBA must be enabled

2 - there is nothing to prevent the user from altering the layout of the PT altogether.

How significant these issues are will depend partly on what it is you're trying to do.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "PivotTable1" Then
    Application.EnableEvents = False
    Target.PivotFields("FieldName").CurrentPage = "RequiredValue"
    Application.EnableEvents = True
End If
End Sub

The above would reside in the Sheet object on which the PT resides, to implement right click on the tab containing the Pivot Table, select View Code and paste the above into the resulting window - modifying the sections in red to meet your own requirements.

**guy_tayeb** · 07-06-2009, 06:59 AM

Hi,

At first thanks a lot.

The big issue is that in the company i work we dont allowed to use the VBA and i cant enable it.

is there any chance that there is a solution that i can use without that?

Thanks,
Guy

**DonkeyOte** · 07-06-2009, 07:04 AM

Not that I can think of I'm afraid... you can't use Data Validation on a Pivot Table nor can you edit any part of a Pivot Table on a protected worksheet... that said just because I can't think of a way to do it does not mean it can't be done so don't give up hope just yet!