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

 

[exceliz]

hi
sorry to drop in on a post..
but I have a slicer dashboard (they are really good tools) but I have various organizations using (or going to be) where they select themselves to view their data and double click through to detail - however I need to be able to protect everyone's data, i.e. organization A cannot click through to organization's B's data? is it possible to have them only being able to select their own orgs?

thanks
Liz

 

[Wasim Akram Khan]

Hello,

I have a similar kind of problem. I want to protect few cells in an excel worksheet. The sheet contains multiple slicers. As soon as i protect the sheet, it is not allowing me to access the slicer, means not allowing me to select the options from the slicer, slicer is also getting locked.

It is quite an urgent requirement . Any solution would be helpful.

Thanks
Wasim

 

[Jerry Sullivan]

Hi Wasim, You can follow the steps that Vidar provided in Post #2 to have a slicer work on protected sheet.

The problem that is the topic of discussion above, is that doing so allows a user the ability to delete the slicer.

 

[SpillerBD]

(wrong thread)

 

[SpillerBD]

hi
sorry to drop in on a post..
but I have a slicer dashboard (they are really good tools) but I have various organizations using (or going to be) where they select themselves to view their data and double click through to detail - however I need to be able to protect everyone's data, i.e. organization A cannot click through to organization's B's data? is it possible to have them only being able to select their own orgs?

thanks
Liz

You need to NOT use the default Pivot Table Option "Save source data with file"
Then use the "Show Report Filter Pages" to create the Worksheets/Pivot Tables that needs to sent to the individuals. Once I have the various sheets, I use a macro to copy the sheets to new individual files.

The macro to send the selected sheets to a designated folder.

'SpltSheets
Sub SplitSelectedWorkSheets()
    Dim ws As Worksheet
    Dim DisplayStatusBar As Boolean
    Dim DestinationPath As Variant
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path & "\"
        .Title = "Select a destination folder or create a new destination."
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            'MsgBox .SelectedItems(1)
            DestinationPath = .SelectedItems(1)
        End If
    End With
    
    DisplayStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = ActiveWindow.SelectedSheets.Count & " Remaining Sheets"
    
    For Each ws In ActiveWindow.SelectedSheets
        Dim NewFileName As String
       
        'Macro-Enabled
        'NewFileName = ThisWorkbook.Path & "\" & ws.Name & ".xlsm"
        'Not Macro-Enabled
            NewFileName = DestinationPath & "\" & ws.Name & ".xlsx"
            ws.Copy
            'ActiveWorkbook.Sheets(1).Name = "Sheet1"
            'ActiveWorkbook.SaveAs Filename:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.SaveAs FileName:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False
    Next
    
    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.DisplayStatusBar = DisplayStatusBar
    Application.ScreenUpdating = True
    Close 'close all files and folders?
End Sub
'SpltSheets
Sub SplitGP()
    Dim ws As Worksheet
    Dim DisplayStatusBar As Boolean
    Dim DestinationPath As Variant
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path & "\"
        .Title = "Select a destination folder or create a new destination."
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            DestinationPath = .SelectedItems(1)
        End If
    End With
    
    DisplayStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = ActiveWindow.SelectedSheets.Count & " Remaining Sheets"
    
    For Each ws In ActiveWindow.SelectedSheets
        Dim NewFileName As String
            NewFileName = DestinationPath & "\" & ws.Cells(5, 1) & ".GP.xlsx"
            ws.Copy
            ActiveWorkbook.SaveAs FileName:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False
    Next
    Application.DisplayAlerts = True
    Application.StatusBar = False
    Application.DisplayStatusBar = DisplayStatusBar
    Application.ScreenUpdating = True
    Close 'close all files and folders?
End Sub

 

[ReignEternal]

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

I have been searching and searching and this fixed my issue. Thank you all