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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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,355
Messages
5,571,693
Members
412,413
Latest member
dvprajapati
Top