protected sheet and slicers enabled

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
672
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
i figured it out
as part of protect code i had to add the additional allowances:
allowfiltering:=true, allowsort:=true etc.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top