How to lock a worksheet and enable the PivotTable refresh data opt
 

I guess the subject says it all. I have a pivottable I want to lock the
worksheet it's in. but still keep the refresh data button available.

Which of the functions should do it? I tried them all with no success.

Saariko

You mean that the worksheet is Protected?
You will likely have to write a macro that Unprotects the Sheet, refreshes
the PivotTable, then Reprotects the sheet. If that's what you are looking for
post back and someone will help you get started.

tj

"Saariko" wrote:

I guess the subject says it all. I have a pivottable I want to lock the
worksheet it's in. but still keep the refresh data button available.

Which of the functions should do it? I tried them all with no success.

Saariko

Something like this should get you started. Note that the AllowPivotTables
part will only work in Excel 2002 or 2003. Leave it out if you are using 2000
or earlier or if you do not want people to be anble to change the PivotTable
structure.

This macro assumes the password assigned to the Worksheet is 12345. If you
don't have a password, you can leave that part off. Also, the name of your
PivotTable is likely not PivotTable5 (as the one below is). To find the name
of your PivotTable, Right-Click on it and choose Table Options.

tj

Sub RefreshPivotTable()

ActiveSheet.Unprotect 12345
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.Protect 12345, AllowUsingPivotTables:=True

End Sub


"Saariko" wrote:

I guess the subject says it all. I have a pivottable I want to lock the
worksheet it's in. but still keep the refresh data button available.

Which of the functions should do it? I tried them all with no success.

Saariko

Sub RefreshProtected()
ActiveWorkbook.Sheets("YourSheetName").Unprotect ("YourPassword")
ActiveWorkbook.RefreshAll
ActiveWorkbook.Sheets("YourSheetName").Protect("Yo urPassword")
End Sub

There are other more detailed levels of protection you can add to the
protection code, but this in its simplest form would do what you are
looking for.

You could assign this to an active control button within your sheet.

Celtic_Avenger


--
Celtic_Avenger
------------------------------------------------------------------------
Celtic_Avenger's Profile: http://www.excelforum.com/member.php...o&userid=14101
View this thread: http://www.excelforum.com/showthread...hreadid=273974