Protecting worksheet but maintaining slicer functionality and usability

[Meagan1972]

Hi,

I have a worksheet that has multple pivot charts link to slicers to eaily allow users to filter data. The problem is when I protect the worksheet i lose the ability for users to use the scliers and when the worksheet is not protected I run the risk of users deleting the slicers. How can I achieve my desired outcome?

Regards
Meagan

 

[Vidar]

Hi Meagan
Right click your slicer and choose Size and Properties -> Properties.
Unselect the "Locked" check box.
And when you protect the worksheet you have to select "Use Pivottable Reports".

Vidar

 

[Meagan1972]

thanks for the reply but when I do that the user is able to delete the slicer which is what i'm trying to avoid....any further ideas?

 

[Vidar]

Not sure if that is possible. At least I don't know a solution for that.
It might be possible to write a VBA macro that disables right click on a slicer.
It's poosible to prevent right click on cells in the worksheet, but there's no such event for the slicer that I know of.

Vidar

 

[Jerry Sullivan]

Hi all,

One approach would be to try to detect when a slicer is deleted by a user interface action then undo that action.

This can be circumvented by not enabling macros, disabling events or deleting the entire sheet; however it could prevent users from unintentionally deleting slicers.

Paste this code to the ThisWorkbook Module of your workbook; then edit the vSlicerNames list to match the Slicer names you want to protect.

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim vSlicerNames() As Variant
    Dim i As Long

    
    vSlicerNames = Array("Slicer_Country", "Slicer_State", "Slicer_City")

    
    For i = LBound(vSlicerNames) To UBound(vSlicerNames)
        On Error Resume Next
        If IsError(ActiveWorkbook.SlicerCaches(vSlicerNames(i)).Name) Then
            With Application
                .EnableEvents = False
                .Undo
                 If IsError(ActiveWorkbook.SlicerCaches(vSlicerNames(i)).Name) Then
                    .Repeat
                    .EnableEvents = True
                    MsgBox "Slicer: " & vSlicerNames(i) & " not found. " & vbCr _
                    & "An attempt to undo the last action did not recover the slicer."
                    Exit For
                Else
                    MsgBox "The last action was undone to recover a deleted slicer"
                End If
                .EnableEvents = True
            End With
        End If
    Next i

End Sub

 

[Abhay Gadiya]

I don't think we need to have a VBA here. Try following steps : Right click slicer and go to size & properties. within it under position and layout click on disable resizing and moving. Further under third option in same window "Properties" click on don't move or size with ce lls and unclick locked. Do this for all slicers. Then to go protect sheet option and under this only click - Select unlocked cells and use pivot reports. DO NOT select edit objects. Once you protect sheet your slicer will be working and can't be deleted. Also your pivot table and chart will update. I hope this solves your issue.

 

[Jerry Sullivan]

I don't think we need to have a VBA here. Try following steps : Right click slicer and go to size & properties. within it under position and layout click on disable resizing and moving. Further under third option in same window "Properties" click on don't move or size with ce lls and unclick locked. Do this for all slicers. Then to go protect sheet option and under this only click - Select unlocked cells and use pivot reports. DO NOT select edit objects. Once you protect sheet your slicer will be working and can't be deleted. Also your pivot table and chart will update. I hope this solves your issue.

Abhay Gadiya, That's much better. Thanks for sharing that.

 

[teylyn]

I can still delete the slicer with these settings. Anyone else get this to work as described, i.e. slicer can be used but not deleted?

 

[Jerry Sullivan]

Hi teylyn,

When I originally tried the settings Abhay Gadiya suggested, I only tried deleting the Slicer by selecting it then hitting the Delete key.

The settings prevent the slicer from being deleted that way.

Recently, while responding to a similar question, I learned that those settings do not prevent deleting of the Slicer through the Right-Click menu (through either "Cut" or "Remove slicername").

As far as I know, the only way to prevent that would be to modify the right-click menu to disable those options.

Here's a link to that thread. Rory offered some RibbonX code that can be used for that purpose.

http://www.mrexcel.com/forum/excel-questions/767331-disable-delete-pivot-table-slicer.html

 

[rstacpoole]

On the basis that a negative result is still a result, I believe that at this time (2016 02 16) this is a bug in Excel 2013. Using the standard options, it is not possible to prevent slicers from being moved and protect it from being deleted. I have tried the none VBA options proposed here - they did not work (for me). I have also read around other sites; their proposals do not work either. On this basis, I'm publishing my file in the "protected but delete possible mode" as the least harmful option