Protecting worksheet but maintaining slicer functionality and usability

Meagan1972

New Member
Joined
Oct 6, 2012
Messages
13
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
 
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
 
Upvote 0

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.
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
Code:
'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
 
Upvote 0

Forum statistics

Threads
1,224,903
Messages
6,181,652
Members
453,059
Latest member
jkevin

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