Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to enable font color on protected worksheet? | Excel Discussion (Misc queries) |