protected sheet and slicers enabled

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
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

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.

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
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
375
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,122,630
Messages
5,597,264
Members
414,133
Latest member
lucid33

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
Top