I have a sheet that contains several pivot tables, with charts running off of them. A drop down menu changes one of the filters in the pivot tables. Everything works just fine, but when I protect the sheet, the drop down cell does not affect the pivot filter. Here is my code (to include some trouble shooting code to see if I could get it to work):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
Dim strField2 As String
Dim strField4 As String
strField = "QUARTER"
strField2 = "FY"
strField4 = "TFM"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("ProductSelect").Address Then
' For Each ws In ThisWorkbook.Worksheets
Set ws = Me
ws.Unprotect
AllowUsingPivotTables = True
For Each pt In ws.PivotTables
With pt.PageFields(strField4)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
ws.Protect
' Next ws
End If
If Target.Address = Range("QtrSelect").Address Then
' For Each ws In ThisWorkbook.Worksheets
Set ws = Me
ws.Unprotect
AllowUsingPivotTables = True
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
ws.Protect
' Next ws
End If
If Target.Address = Range("FYSelect").Address Then
' For Each ws In ThisWorkbook.Worksheets
Set ws = Me
ws.Unprotect
AllowUsingPivotTables = True
For Each pt In ws.PivotTables
With pt.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
ws.Protect
' Next ws
End If
ws.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Please help...
Bookmarks