Protect sheet and disable manual edits to pivot table but allow Slicers

[ardykav]

Not sure if this is possible but what i want to do is as follows:
Unprotect sheet, refresh data on pivot table on sheet, filter out 3 items and then protect sheet.
However after the sheet is protected I want it to be editeable by a Slicer but the user cannot manually override any data in the pivot table.

Sub Protectandset()

    Dim DialogMsg As Integer
    Dim wb As Workbook
    Dim ms As Worksheet
    Dim st As Worksheet
 
       
     
    
    Set wb = ActiveWorkbook
    Set st = wb.Sheets("Source")
    Set ms = wb.Sheets("Summary")
    'emptyvalue = ""
    ms.Unprotect ("password123")
     Range("B4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Production Order Number"). _
        ClearAllFilters
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Production Order Number")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
        .PivotItems("Break").Visible = False
        
    End With
  '  st.Unprotect ("password123")
 
    ms.Protect ("password123")
  '  End If
End Sub

 

[ardykav]

I did my own workaround where I made the Pivot table tab very hidden and then made a mirror tab with formulas of it and locked that down and moved the slicer over.

 

[liamlarmour]

I did my own workaround where I made the Pivot table tab very hidden and then made a mirror tab with formulas of it and locked that down and moved the slicer over.

Hi, just wondering if you've had any further luck with this one? I've a similar issue but because the pivot won't filter in new items on the row fields, the slicer won't update to show them or filter out 'blank'.

 

[ardykav]

Yes I did mate, I used this code, might be a bit of unncessary in it but does the job. The allowusing pivot tables true bit if the important part

Sub Allowupdate()
Dim ms As Worksheet
 Dim xpt As PivotTable
Set ms = Sheets("Sheet1")
ms.Unprotect ("12345")
ActiveWorkbook.RefreshAll
With ActiveSheet
        .Protect UserInterFaceOnly:=True
        For Each xpt In .PivotTables
            xpt.RefreshTable
        Next xpt
    End With
ms.Protect ("12345"), AllowUsingPivotTables:=True

End Sub