Problem with pivot table when workbook is protected

[Raj]

Hi,

One of the sheets of a workbook has a pivot table. All works fine till
the workbook is protected. When the workbook is protected and opened:
a message box appears with the following log:

Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\rsp\excel\1testprotection cee.xls'
The following is a list of repairs:
PivotTable report 'PT2' on '[1testprotection cee.xls]Summary' was
discarded due to integrity problems.

The workbook.open event has the Workbook.unprotect code at the
beginning and workbook.protect code at the end. I did this as the
workbook.open event was failing at the following line:
Worksheets("Change log").Visible = False
(I wanted to hide this sheet on opening the workbook)

Thanks in advance for the help.

Regards,
Raj

 

[Raj]

Hi,

One of the sheets of a workbook has a pivot table. All works fine till
the workbook is protected. When the workbook is protected and opened:
a message box  appears with the following log:

Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\rsp\excel\1testprotection cee.xls'
The following is a list of repairs:
PivotTable report 'PT2' on '[1testprotection cee.xls]Summary' was
discarded due to integrity problems.

The workbook.open event has the Workbook.unprotect code at the
beginning and workbook.protect code at the end. I did this as the
workbook.open event was failing at the following line:
Worksheets("Change log").Visible = False
(I wanted to hide this sheet on opening the workbook)

Thanks in advance for the help.

Regards,
Raj

Some more information for those who could help:

The worksheets have code in their activate and deactivate events. This
is also true of the worksheet having the pivot table. I am using C
Pearson's code for hiding the sheets in the workbook before close
event and unhiding the sheets in the workbook open event. The workbook
is protected with a password. I have a feeling that the problem is
occurring when the close event is fired. When the sheets are being
hidden, there is something that is corrupting the file. Because on
opening the file next time the "unable to read" and corrupt message
above appears. I tried Application.enableevents = false and
Application.enableevents = true just before and after the hide code
in the workbook close event to see if that helped. Even that did not
help... Any clue on why this could be happenning and any workarounds?

The requirement is this: A workbook should have all its sheets hidden
except one when it is opened without enabling macros. There should be
no way to unhide the worksheets till macros are enabled. When macros
are enabled, the user should be able to access the sheets. This works
wonderfully well with the code at cpearson site. The problem starts
when I try protecting the workbook. The workbook gets corrupted and
the message as above appears.


Please Help.

Thanks in Advance.

Regards,
Raj