RePost: Protect
 

I want to protect all the worksheets
however leave the pivot tables for Filtering

+ I have excel 2000.

Is it still possible?

RePost: Protect
 

Assuming the Pivot Tables are already in place, you can use the
workbook_Open event to set the EnablePivotTable property to true. You would
also need to set UserInterFaceOnly to true for the protect method.

From the help on EnablePivotTable

-----------------------------------------------

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

----------------------------------------------

so you would do something like

Private Sub Workbook_Open
Dim sh as Worksheet, sh1 as Worksheet
set sh1 = ActiveSheet
for each sh in Thisworkbook.Worksheets
sh.Select
sh.EnablePivotTable = True
sh.Protect Contents:=True, UserInterfaceOnly:=True
Next
sh1.Activate
End Sub

Use the workbook_Open Event

See Chip Pearson's page on Events if not familiar
http://www.cpearson.com/excel/events.htm

See this article for additional info (pertinent to xl2000 as well)

http://support.microsoft.com/kb/169106/en-us

--
Regards,
Tom Ogilvy



"flow23" wrote in message
...
I want to protect all the worksheets
however leave the pivot tables for Filtering

+ I have excel 2000.

Is it still possible?

RePost: Protect
 

Thanks

Its works fine.. except when it selects the last sheet . an error occurs

" Run time error 1004
: Method "select of object_worksheet failed"

Any ideas why?



"Tom Ogilvy" wrote:

Assuming the Pivot Tables are already in place, you can use the
workbook_Open event to set the EnablePivotTable property to true. You would
also need to set UserInterFaceOnly to true for the protect method.

From the help on EnablePivotTable

-----------------------------------------------

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

----------------------------------------------

so you would do something like

Private Sub Workbook_Open
Dim sh as Worksheet, sh1 as Worksheet
set sh1 = ActiveSheet
for each sh in Thisworkbook.Worksheets
sh.Select
sh.EnablePivotTable = True
sh.Protect Contents:=True, UserInterfaceOnly:=True
Next
sh1.Activate
End Sub

Use the workbook_Open Event

See Chip Pearson's page on Events if not familiar
http://www.cpearson.com/excel/events.htm

See this article for additional info (pertinent to xl2000 as well)

http://support.microsoft.com/kb/169106/en-us

--
Regards,
Tom Ogilvy



"flow23" wrote in message
...
I want to protect all the worksheets
however leave the pivot tables for Filtering

+ I have excel 2000.

Is it still possible?