I have an Excel 2002 spreadsheet which contains a Pivot Table. The Pivot
Table begins in cell B6. I have other functions which I have entered into
cells B2-B5. My goal is to lock and password protect cells B2-B5 but still
allow the Pivot Table to be refreshed using the refresh icon on the toolbar.
I have locked B2-B5 and unlocked the rest of the worksheet then turned on
Protect Sheet and the Refresh Data icon is no longer active. I have also
tried checking the box Use Pivot Table Reports under Protect Sheet and again
the Refresh Data icon is no longer active. Anybody know how to address this
issue?

You could record code as you unprotect the sheet, refresh the pivot
table, and reprotect the sheet.

Then, run that code as required, e.g. after you've updated the source data.

KJThams wrote:
I have an Excel 2002 spreadsheet which contains a Pivot Table. The Pivot
Table begins in cell B6. I have other functions which I have entered into
cells B2-B5. My goal is to lock and password protect cells B2-B5 but still
allow the Pivot Table to be refreshed using the refresh icon on the toolbar.
I have locked B2-B5 and unlocked the rest of the worksheet then turned on
Protect Sheet and the Refresh Data icon is no longer active. I have also
tried checking the box Use Pivot Table Reports under Protect Sheet and again
the Refresh Data icon is no longer active. Anybody know how to address this
issue?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Thank you for the reply. This is a workbook that I have created for someone
else's use. They are not Excel literate therefore I need to password protect
those cells that have functions written into them. I have already found out
the hard way that they will erase those functions if I do not protect them.
Any other suggestions?

"Debra Dalgleish" wrote:

You could record code as you unprotect the sheet, refresh the pivot
table, and reprotect the sheet.

Then, run that code as required, e.g. after you've updated the source data.

KJThams wrote:
I have an Excel 2002 spreadsheet which contains a Pivot Table. The Pivot
Table begins in cell B6. I have other functions which I have entered into
cells B2-B5. My goal is to lock and password protect cells B2-B5 but still
allow the Pivot Table to be refreshed using the refresh icon on the toolbar.
I have locked B2-B5 and unlocked the rest of the worksheet then turned on
Protect Sheet and the Refresh Data icon is no longer active. I have also
tried checking the box Use Pivot Table Reports under Protect Sheet and again
the Refresh Data icon is no longer active. Anybody know how to address this
issue?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html