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

hi,

look at the Protect method and UserInterfaceOnly

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

--
isabelle



Le 2012-02-07 19:40, michaelnol a écrit :
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


On Feb 8, 2:00*pm, isabelle wrote:
hi,

look at the Protect method and UserInterfaceOnly

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

--
isabelle

Le 2012-02-07 19:40, michaelnol a écrit :



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- Hide quoted text -

- Show quoted text -

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

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

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

"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,

"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

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

(I have not installed 2010 yet...)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc