Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protecting A Worksheet That Contains A Pivot Table (Excel 2002 SP3)

    I have a workbook which contains claims recovery data by year going back to 2003. Each worksheet records collections, listed in order of receipt date, for a given year. A new reporting requirement made it necessary for me to include a list of attorneys and the total fees which were paid to them in each year ... pivot tables worked perfectly for this -- my first application of them. However, I discovered that turning the 2005 worksheet protection back on after the modification, prevented the pivot table from being updated.
    Since the worksheets contain formulas which must be locked to prevent overwriting as information is added, daily, and the pivot table must appear on the same worksheet (first pages of the reports - audit requirement), I seem to be in a catch 22. Does anyone know a way of locking the cells without making the pivot tables useless?

    Thanks for your help,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Protecting A Worksheet That Contains A Pivot Table (Excel 2002 SP3)

    You could create a macro that

    - Unlocks the worksheet
    - Updates the pivot table
    - Locks the worksheet again

    If you assign this macro to a custom toolbar button, it is easy for the user to update the pivot table without compromising the protection. Example code:

    Sub RefreshPivot()
    With ActiveSheet
    .Unprotect
    .PivotTables(1).PivotCache.Refresh
    .Protect
    End With
    End Sub

    You can add arguments to the .Unprotect and .Protect lines, such as a password - click in one of these words and press F1 to learn more about them.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protecting A Worksheet That Contains A Pivot Table (Excel 2002 SP3)

    Hans,

    As always, you are most helpful. Thank you, especially for the code sample.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •