Enable Slicer Data Refresh in Locked S/S

  • Thread starter Thread starter michaelnol
  • Start date Start date
M

michaelnol

I have a spreadsheet connected to an anlysis cube that is updated/
changed using slicers. I want to be able to lock the spreadsheet so no
editing etc can take place on the s/s but still have the slicers
remain active and working

I have looked high and low to a solution to this problem with no luck,
so any help would be useful, thanks
 
Thanks Isabelle

I have had a play with the protect method and UserInterfaceOnly but it
does not seem to keep the slicers enabled and the cells uneditable.

I am quite new at VB, do you have any suggested code I could try?

Thanks
 
michaelnol laid this down on his screen :
I have had a play with the protect method and UserInterfaceOnly but it
does not seem to keep the slicers enabled and the cells uneditable
Click to expand...

What, exactly, are 'slicers'? If VBA then note that the
UserInterfaceOnly parameter of sheet protection is nn-persistent. That
means you must reset protection each time the file is opened. In order
to reset protection you MUST remove any existing protection beforehand.

(IOW, you can't 'add' additional protection to a protected sheet)

Example code:

Const gsPWRD As String = " " 'edit to suit

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks
.Unprotect gsPWRD
.Protect Password:=gsPWRD, UserInterfaceOnly:=True
End With 'Wks
End Sub 'ResetProtection
 
Garry,

Re: "What, exactly, are 'slicers'?"

From the xl2010 help file...
"New in Excel 2010, slicers offer a highly visual way to filter the data in PivotTables. Once you
insert a slicer, you use buttons to quickly segment and filter the data to display just what you
need. In addition, when you apply more than one filter to your PivotTable, you no longer have to
open a list to see which filters are applied to the data. Instead, it is shown there on the screen
in the slicer. You can make slicers match your workbook formatting and easily reuse them in other
PivotTables, PivotCharts, and cube functions."

Never used them.
'---
Jim Cone
 
Jim Cone presented the following explanation :
Garry,

Re: "What, exactly, are 'slicers'?"

From the xl2010 help file...
"New in Excel 2010, slicers offer a highly visual way to filter the data in
PivotTables. Once you insert a slicer, you use buttons to quickly segment and
filter the data to display just what you need. In addition, when you apply
more than one filter to your PivotTable, you no longer have to open a list to
see which filters are applied to the data. Instead, it is shown there on the
screen in the slicer. You can make slicers match your workbook formatting and
easily reuse them in other PivotTables, PivotCharts, and cube functions."

Never used them.
'---
Jim Cone
Click to expand...

Thanks, Jim! So how does this relate to sheet protection and/or VBA?

(I have not installed 2010 yet...)
 
As I said "Never used them".
A quick look at help says the Siicers parent is the workbook not the worksheet.
There is also a SlicerCache in there somewhere.
Looks like a convention hierarchy of a slicers collection with slicer objects.
'---
Jim Cone
(he discovered he was only looking at the tail of the elephant)
 
Jim Cone explained on 2/7/2012 :
As I said "Never used them".
A quick look at help says the Siicers parent is the workbook not the
worksheet.
There is also a SlicerCache in there somewhere.
Looks like a convention hierarchy of a slicers collection with slicer
objects.
'---
Jim Cone
(he discovered he was only looking at the tail of the elephant)
Click to expand...

Thanks again, Jim!
Ok, so we may be barking up the wrong tree with putting focus on
protection parameters and UserInterfaceOnly. One would think, though,
that Excel native code would respect that setting, IMO!
 
Jim Cone explained on 2/7/2012 :


Thanks again, Jim!
Ok, so we may be barking up the wrong tree with putting focus on
protection parameters and UserInterfaceOnly. One would think, though,
that Excel native code would respect that setting, IMO!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

I guess it needs to be some code that onclick of a slicer the
protection is removed to allow the data to refresh and then the
protection is reapplied. It is the onclick of a slicer that has me
stumped....
 
michaelnol was thinking very hard :
I guess it needs to be some code that onclick of a slicer the
protection is removed to allow the data to refresh and then the
protection is reapplied. It is the onclick of a slicer that has me
stumped....
Click to expand...

My thinkng is that the protection parameters should be respected by
built-in processes and so, if correctly applied, the data should
refresh. Did you try 'resetting' the protection as per my suggestion?
 
hi michael,

there is an example here for excel 2010: http://msdn.microsoft.com/en-us/library/ff197311.aspx

but i don't know if this applies to a slicer
is that slicer is be considered as an object OLAP Pivot ?


Sub ProtectionOptions()

ActiveSheet.Unprotect

' Allow pivot tables to be manipulated on a protected worksheet.
If ActiveSheet.Protection.Allow UsingPivotTables = False Then
ActiveSheet.Protect Password:=MyPassword, AllowUsingPivotTables:=True
End If

MsgBox "Pivot tables can be manipulated on the protected worksheet."

End Sub


--
isabelle



Le 2012-02-08 15:37, michaelnol a écrit :
 
Isabelle, nice catch! I forgot all about that.

If anyone's interested, the protection schema I use follows below. I
basically lets you set default protection parameters using positive
nums for True, negative nums for False. It also includes a special
setting for allowing selection as the last enum value. The settings are
stored in an array which can be revised at runtime as desired, OR can
be integrated with UI settings stored in local scope defined name
for each sheet. That means you can use different protection schemes for
different sheets.

(In Michael's case, he needs to change -14 to 14)

I welcome any feedback/questions...
**Watch for line wraps**

Option Explicit

'Copy the following line into Auto_Open or Workbook_Open
'to initialize the default permissions for the wkb,
'and to reset protection on all sheets at startup.
'**Note that some of the settings do not persist after the wkb is
closed,
'and so reset must be done each time the wkb is opened.
'==================================================
' Call Set_DefaultWksProtection: Protect_AllSheets
'==================================================

'Use enum for changing option settings prior to running WksProtect,
'so you know which permissions you're changing.
'Example: gvaWksProtectOpts(WksProtection.wpAllowFormatCells) = False
'When done, run Set_DefaultWksProtection() to restore defaults
Enum WksProtection
wpDrawingObjects = 1
wpContents = 2
wpScenarios = 3
wpUserInterfaceOnly = 4
wpAllowFiltering = 5
wpAllowFormatCols = 6
wpAllowFormatRows = 7
wpAllowFormatCells = 8
wpAllowDeleteCols = 9
wpAllowDeleteRows = 10
wpAllowInsertCols = 11
wpAllowInsertRows = 12
wpAllowInsertHLinks = 13
wpAllowPivotTables = 14
wpEnableAutoFilter = 15
wpEnableOutlining = 16
wpEnableSelection = 17 '0=xlNoRestrictions; 1=xlUnlockedCells;
2=xlNoSelection
End Enum

Public gvaWksProtectOpts(1 To 17)
Const gsDEF_WKS_PROTECTION As String =
"-1,2,3,4,5,6,7,8,-9,-10,-11,-12,-13,-14,15,16,0"


Sub Set_DefaultWksProtection()
Dim i As Integer, vwpOpts As Variant
vwpOpts = Split(gsDEF_WKS_PROTECTION, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1
gvaWksProtectOpts(i) = (vwpOpts(i - 1) > 0)
Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) =
vwpOpts(UBound(vwpOpts))
End Sub

Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if
there isn't one.
'
' Arguments: WksName [In] Optional. The name of the sheet to be
protected.
' Defaults to ActiveSheet.Name if missing.

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.Version) >= 10 Then
.Protect Password:=PWRD, _

DrawingObjects:=gvaWksProtectOpts(WksProtection.wpDrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpContents), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScenarios), _

UserInterfaceOnly:=gvaWksProtectOpts(WksProtection.wpUserInterfaceOnly),
_

AllowFiltering:=gvaWksProtectOpts(WksProtection.wpAllowFiltering), _

AllowFormattingColumns:=gvaWksProtectOpts(WksProtection.wpAllowFormatCols),
_

AllowFormattingRows:=gvaWksProtectOpts(WksProtection.wpAllowFormatRows),
_

AllowFormattingCells:=gvaWksProtectOpts(WksProtection.wpAllowFormatCells),
_

AllowDeletingColumns:=gvaWksProtectOpts(WksProtection.wpAllowDeleteCols),
_

AllowDeletingRows:=gvaWksProtectOpts(WksProtection.wpAllowDeleteRows),
_

AllowInsertingColumns:=gvaWksProtectOpts(WksProtection.wpAllowInsertCols),
_

AllowInsertingRows:=gvaWksProtectOpts(WksProtection.wpAllowInsertRows),
_

AllowInsertingHyperlinks:=gvaWksProtectOpts(WksProtection.wpAllowInsertHLinks),
_

AllowUsingPivotTables:=gvaWksProtectOpts(WksProtection.wpAllowPivotTables)
Else
.Protect Password:=PWRD, _

DrawingObjects:=gvaWksProtectOpts(WksProtection.wpDrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpContents), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScenarios), _

UserInterfaceOnly:=gvaWksProtectOpts(WksProtection.wpUserInterfaceOnly)
End If
.EnableAutoFilter =
gvaWksProtectOpts(WksProtection.wpEnableAutoFilter)
.EnableOutlining =
gvaWksProtectOpts(WksProtection.wpEnableOutlining)

Select Case
CLng(gvaWksProtectOpts(WksProtection.wpEnableSelection))
Case 0: .EnableSelection = xlNoRestrictions '0
Case 1: .EnableSelection = xlUnlockedCells '1
Case 2: .EnableSelection = xlNoSelection '-4142
End Select
End With
End Sub 'wksProtect()

Sub wksUnprotect(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
Wks.Unprotect PWRD
End Sub

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD
wksProtect Wks
End Sub

Sub Protect_AllSheets()
Dim Wks As Worksheet
Application.ScreenUpdating = False
For Each Wks In ThisWorkbook.Worksheets: ResetProtection Wks: Next
Application.ScreenUpdating = True
End Sub

Sub Unprotect_AllSheets()
Dim Wks As Worksheet
Application.ScreenUpdating = False
For Each Wks In ThisWorkbook.Worksheets: wksUnprotect Wks: Next
Application.ScreenUpdating = True
End Sub
 
i can not test your solution at this time because i have not installed xl2010
but when i see that there are vba's solutions i'm much encouraged to upgrade my old xl,
it's very nice to you, thank you very much Garry

--
isabelle


Le 2012-02-08 23:42, GS a écrit :
Isabelle, nice catch! I forgot all about that.

If anyone's interested, the protection schema I use follows below.
Click to expand...

I basically lets you set default protection parameters using positive nums for True,

negative nums for False. It also includes a special setting for allowing selection as the last enum value.

The settings are stored in an array which can be revised at runtime as desired,

OR can be integrated with UI settings stored in local scope defined name for each sheet.

That means you can use different protection schemes for different
sheets.

(In Michael's case, he needs to change -14 to 14)

I welcome any feedback/questions...
**Watch for line wraps**

Option Explicit

'Copy the following line into Auto_Open or Workbook_Open
'to initialize the default permissions for the wkb,
'and to reset protection on all sheets at startup.
'**Note that some of the settings do not persist after the wkb is closed,
'and so reset must be done each time the wkb is opened.
'==================================================
' Call Set_DefaultWksProtection: Protect_AllSheets
'==================================================

'Use enum for changing option settings prior to running WksProtect,
'so you know which permissions you're changing.
'Example: gvaWksProtectOpts(WksProtection.wpAllowFormatCells) = False
'When done, run Set_DefaultWksProtection() to restore defaults
Enum WksProtection
wpDrawingObjects = 1
wpContents = 2
wpScenarios = 3
wpUserInterfaceOnly = 4
wpAllowFiltering = 5
wpAllowFormatCols = 6
wpAllowFormatRows = 7
wpAllowFormatCells = 8
wpAllowDeleteCols = 9
wpAllowDeleteRows = 10
wpAllowInsertCols = 11
wpAllowInsertRows = 12
wpAllowInsertHLinks = 13
wpAllowPivotTables = 14
wpEnableAutoFilter = 15
wpEnableOutlining = 16
wpEnableSelection = 17 '0=xlNoRestrictions; 1=xlUnlockedCells; 2=xlNoSelection
End Enum

Public gvaWksProtectOpts(1 To 17)
Const gsDEF_WKS_PROTECTION As String = "-1,2,3,4,5,6,7,8,-9,-10,-11,-12,-13,-14,15,16,0"


Sub Set_DefaultWksProtection()
Dim i As Integer, vwpOpts As Variant
vwpOpts = Split(gsDEF_WKS_PROTECTION, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1
gvaWksProtectOpts(i) = (vwpOpts(i - 1) > 0)
Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) = vwpOpts(UBound(vwpOpts))
End Sub

Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if there isn't one.
'
' Arguments: WksName [In] Optional. The name of the sheet to be protected.
' Defaults to ActiveSheet.Name if missing.

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.Version) >= 10 Then
.Protect Password:=PWRD, _
DrawingObjects:=gvaWksProtectOpts(WksProtection.wpDrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpContents), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScenarios), _
UserInterfaceOnly:=gvaWksProtectOpts(WksProtection.wpUserInterfaceOnly), _
AllowFiltering:=gvaWksProtectOpts(WksProtection.wpAllowFiltering), _
AllowFormattingColumns:=gvaWksProtectOpts(WksProtection.wpAllowFormatCols), _
AllowFormattingRows:=gvaWksProtectOpts(WksProtection.wpAllowFormatRows), _
AllowFormattingCells:=gvaWksProtectOpts(WksProtection.wpAllowFormatCells), _
AllowDeletingColumns:=gvaWksProtectOpts(WksProtection.wpAllowDeleteCols), _
AllowDeletingRows:=gvaWksProtectOpts(WksProtection.wpAllowDeleteRows), _
AllowInsertingColumns:=gvaWksProtectOpts(WksProtection.wpAllowInsertCols), _
AllowInsertingRows:=gvaWksProtectOpts(WksProtection.wpAllowInsertRows), _
AllowInsertingHyperlinks:=gvaWksProtectOpts(WksProtection.wpAllowInsertHLinks), _
AllowUsingPivotTables:=gvaWksProtectOpts(WksProtection.wpAllowPivotTables)
Else
.Protect Password:=PWRD, _
DrawingObjects:=gvaWksProtectOpts(WksProtection.wpDrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpContents), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScenarios), _
UserInterfaceOnly:=gvaWksProtectOpts(WksProtection.wpUserInterfaceOnly)
End If
.EnableAutoFilter = gvaWksProtectOpts(WksProtection.wpEnableAutoFilter)
.EnableOutlining = gvaWksProtectOpts(WksProtection.wpEnableOutlining)

Select Case CLng(gvaWksProtectOpts(WksProtection.wpEnableSelection))
Case 0: .EnableSelection = xlNoRestrictions '0
Case 1: .EnableSelection = xlUnlockedCells '1
Case 2: .EnableSelection = xlNoSelection '-4142
End Select
End With
End Sub 'wksProtect()

Sub wksUnprotect(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
Wks.Unprotect PWRD
End Sub

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD
wksProtect Wks
End Sub

Sub Protect_AllSheets()
Dim Wks As Worksheet
Application.ScreenUpdating = False
For Each Wks In ThisWorkbook.Worksheets: ResetProtection Wks: Next
Application.ScreenUpdating = True
End Sub

Sub Unprotect_AllSheets()
Dim Wks As Worksheet
Application.ScreenUpdating = False
For Each Wks In ThisWorkbook.Worksheets: wksUnprotect Wks: Next
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Back
Top