Pivot Table not refreshing on worksheet protected with userinterfaceonly =true

[vijyender]

I have workbook which contains many vba codes, many pivot tables in different worksheets and more than 100 worksheets. My problem is that


1. if I protect all the worksheets in workbook_open event:


Private Sub Workbook_Open()

Dim wSheet As Worksheet


For Each wSheet In Worksheets

wSheet.Protect Password:="khatkar", UserInterfaceOnly:=True

Next wSheet


End Sub


And try to refresh my pivot table reports with this code:


Private Sub Worksheet_Activate()

Dim pt As PivotTable


For Each pt In Me.PivotTables

pt.RefreshTable

Next pt

End Sub


Then it shows "Run-time error 1004: RefreshTable method of PivotTable class failed".


2. But if I protect sheets using this code:


Private Sub Workbook_Open()

Dim ws As Worksheet

Set WSArray = Sheets(Array("MASTERWORK", "PENDDATA", "PENDRPT_VILLWISE"))

For Each ws In WSArray

ws.Protect password:="khatkar", UserInterfaceOnly:=True

Next

End Sub


And try to update pivot table with the second (above) code, it works fine.


I can't use the second Protection code because workbook contains more than 100 sheets and I will take a long time type the names of all the sheets in the code.


Please help me why even after setting "userinterfaceonly" to true in the first protection code, my pivot table are updating. Please help me out.

 

[SirJB7]

Hi, vijyender!

Sorry it's in spanish but I use Excel in that language and I'm a little lazy to traduce all the paragraph... but I think you may be able to handle it or reproduce in your local idiom.

From the VBA help window, I typed userinterfaceonly and I got this. I think it'll aid and guide you.

-----

Referencia del programador de Excel 2010 > Referencia de modelos de objetos de Excel > Objeto Worksheet > Propiedades

Referencia del programador de Excel

WorkSheet.EnablePivotTable (propiedad)

True si están activados los controles y acciones de tabla dinámica cuando se activa la protección de la interfaz del usuario. Boolean de lectura y escritura.

Sintaxis


expresión.EnablePivotTable


expresión Variable que representa un objeto WorkSheet.


Comentarios


Esta propiedad se aplica a cada hoja de cálculo y no se guarda con la hoja ni la sesión.


Debe haber un número suficiente de celdas desbloqueadas debajo y a la derecha del informe de tabla dinámica para que Microsoft Excel actualice y muestre el informe de tabla dinámica.


Ejemplo


Este ejemplo activa los controles de tabla dinámica en una hoja de cálculo protegida.


Visual Basic para aplicaciones

ActiveSheet.EnablePivotTable = True

ActiveSheet.Protect contents:=True, userInterfaceOnly:=True


© 2010 Microsoft Corporation. Reservados todos los derechos.


-----

Regards!