Issue with Protected sheet using VBA and Slicers

[Binbs]

Hi all,

I'm using the code below in a spreadsheet that uses a pivot table and slicers. I don't want users to resize columns so I'm protecting the sheet.

I included Sheets("CST View").Unprotect "password" to unprotect the sheet and allow the code to run. This works.

I also included Sheets("CST View").Protect "password" to re-protect the sheet after the code runs. This seems to work fine when the cells in the range ("H7:K7") are edited.

However, when I use any of the slicers, the worksheet is unprotected and then not re-protected.

I'm not having luck getting the slicers to work properly with the VBA code below when the worksheet is protected. Can anyone see an issue with my code below?

Sub Worksheet_Change(ByVal Target As Range)


    
Sheets("CST View").Unprotect "password"
    Dim KeyCells As Range
    
    Set KeyCells = Range("H7:K7")
    
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
    
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

Sheets("CST View").Protect "password"

End If



End Sub

 

[Coding4Fun]

Sounds like you need to move your call to re-protect the sheet to outside of the If statement

Try changing this

Sub Worksheet_Change(ByVal Target As Range)

Sheets("CST View").Unprotect "password"
    Dim KeyCells As Range
   
    Set KeyCells = Range("H7:K7")
   
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
   
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

        Sheets("CST View").Protect "password"

End If

End Sub

To this

Sub Worksheet_Change(ByVal Target As Range)
   
Sheets("CST View").Unprotect "password"
    Dim KeyCells As Range
   
    Set KeyCells = Range("H7:K7")
   
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
   
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

        Sheets("CST View").Protect "password"

End If

Sheets("CST View").Protect "password"

End Sub

Or perhaps to this

Sub Worksheet_Change(ByVal Target As Range)
   
    Dim KeyCells As Range
   
    Set KeyCells = Range("H7:K7")
   
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        Sheets("CST View").Unprotect "password"
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

        Sheets("CST View").Protect "password"

End If

End Sub

 

[Binbs]

Sounds like you need to move your call to re-protect the sheet to outside of the If statement

Try changing this

Sub Worksheet_Change(ByVal Target As Range)

Sheets("CST View").Unprotect "password"
    Dim KeyCells As Range
  
    Set KeyCells = Range("H7:K7")
  
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
  
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

        Sheets("CST View").Protect "password"

End If

End Sub

To this

Sub Worksheet_Change(ByVal Target As Range)
  
Sheets("CST View").Unprotect "password"
    Dim KeyCells As Range
  
    Set KeyCells = Range("H7:K7")
  
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
  
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

        Sheets("CST View").Protect "password"

End If

Sheets("CST View").Protect "password"

End Sub

Or perhaps to this

Sub Worksheet_Change(ByVal Target As Range)
  
    Dim KeyCells As Range
  
    Set KeyCells = Range("H7:K7")
  
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        Sheets("CST View").Unprotect "password"
        Worksheets("CST View").PivotTables("AutoOL").PivotCache.Refresh

        Sheets("CST View").Protect "password"

End If

End Sub

Thank you!