I use pivot tables in my spreadsheet.

When I protect the sheets to prevent changes to locked cells, the pivot
tables wont allow data refresh?!

Even when I check all the boxes when protecting a sheet - including the
pivot tables check box, still the pivot tables cannot be activated.

The pivot table cells are all unlocked as is the data it reads from.

Can anyone help me?

Tahnks,

Roger

You can't refresh the pivot table if the sheet is protected, no matter
what protection settings you used.
In the code you can unprotect the sheet, refresh the pivot table, then
protect the sheet.

Roger on Excel wrote:
I use pivot tables in my spreadsheet.

When I protect the sheets to prevent changes to locked cells, the pivot
tables wont allow data refresh?!

Even when I check all the boxes when protecting a sheet - including the
pivot tables check box, still the pivot tables cannot be activated.

The pivot table cells are all unlocked as is the data it reads from.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Thanks Debra,

Can you suggest code for me to use on a protected sheet - i can assign it as
a macro to refresh the pivot table with a button by the pivot table?

Best regards,

Roger

"Debra Dalgleish" wrote:

You can't refresh the pivot table if the sheet is protected, no matter
what protection settings you used.
In the code you can unprotect the sheet, refresh the pivot table, then
protect the sheet.

Roger on Excel wrote:
I use pivot tables in my spreadsheet.

When I protect the sheets to prevent changes to locked cells, the pivot
tables wont allow data refresh?!

Even when I check all the boxes when protecting a sheet - including the
pivot tables check box, still the pivot tables cannot be activated.

The pivot table cells are all unlocked as is the data it reads from.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Sub RefreshPivot()
With ActiveSheet
.Unprotect Password:="MyPwd"
.PivotTables(1).RefreshTable
.Protect Password:="MyPwd", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowUsingPivotTables:=True
End With
End Sub


Roger on Excel wrote:
Thanks Debra,

Can you suggest code for me to use on a protected sheet - i can assign it as
a macro to refresh the pivot table with a button by the pivot table?

Best regards,

Roger

"Debra Dalgleish" wrote:


You can't refresh the pivot table if the sheet is protected, no matter
what protection settings you used.
In the code you can unprotect the sheet, refresh the pivot table, then
protect the sheet.

Roger on Excel wrote:

I use pivot tables in my spreadsheet.

When I protect the sheets to prevent changes to locked cells, the pivot
tables wont allow data refresh?!

Even when I check all the boxes when protecting a sheet - including the
pivot tables check box, still the pivot tables cannot be activated.

The pivot table cells are all unlocked as is the data it reads from.


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com