protected sheet and slicers enabled

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
364
Office Version
  1. 365
Platform
  1. Windows
hi
i protected the sheets in my workbook
i unlocked the slicers
i have a PQ refresh macro which protecs/unprotects to refresh
after each refresh the slicers get locked again

what would i add to my code to leave my slicers enabled?
Rich (BB code):
Sub RefreshPQActiveWorkbook()
        
If MsgBox("Do you want to refresh?", vbOKCancel + vbQuestion) = vbCancel Then
    Exit Sub
End If

Dim ActSheet As String
ActSheet = ActiveSheet.Name

'ThisWorkbook.Activate

Application.ScreenUpdating = False

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call WSUnProtect(ws)
    Next ws

        ActiveWorkbook.RefreshAll
    
       
    For Each ws In ActiveWorkbook.Worksheets
        Call WSProtect(ws)
    Next ws
    
    Worksheets(ActSheet).Activate
    
    MsgBox "Refresh complete", vbInformation


    'Turn off events, close SD, and Turn events back on
    Application.EnableEvents = False
        Application.EnableEvents = True
    Application.ScreenUpdating = True

    ActiveSheet.Activate
    
End Sub
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
364
Office Version
  1. 365
Platform
  1. Windows
hi
perhaps its in the protection that i need to correct to allow slicers:
Rich (BB code):
Sub WSProtect(ws As Worksheet)
    With ws
        .Protect Password:="MDC1049", DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlUnlockedCells
    End With
End Sub
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
364
Office Version
  1. 365
Platform
  1. Windows
i figured it out
as part of protect code i had to add the additional allowances:
allowfiltering:=true, allowsort:=true etc.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,118,309
Messages
5,571,477
Members
412,395
Latest member
nielsvanlit
Top