+ Reply to Thread
Results 1 to 4 of 4

Pivot Table Protection

  1. #1
    mikeb
    Guest

    Pivot Table Protection

    Pivot Tables in my workbooks cannot be refreshed if the sheet is protected.

    Here's what I've done:

    Set up my worksheet.
    Formatted cells that I wish to be locked.
    Selected Tools>Protect Workbook
    Selected Tools>Protect Worksheet
    Clicked the following checkboxes: Select unlocked cells, Use Pivot Table
    reports

    The actual area of the Pivot Table in my worksheet is unlocked. Whe I select
    the pivot table in the protected sheet I cannont refresh, but I can format
    and create charts.

    Any help would be appreciated.

    Mike

  2. #2
    Debra Dalgleish
    Guest

    Re: Pivot Table Protection

    You could record code as you unprotect the sheet, refresh the pivot
    table, and reprotect the sheet.

    Then, run that code as required, e.g. after you've updated the source
    data, or when the pivot sheet is activated.

    mikeb wrote:
    > Pivot Tables in my workbooks cannot be refreshed if the sheet is protected.
    >
    > Here's what I've done:
    >
    > Set up my worksheet.
    > Formatted cells that I wish to be locked.
    > Selected Tools>Protect Workbook
    > Selected Tools>Protect Worksheet
    > Clicked the following checkboxes: Select unlocked cells, Use Pivot Table
    > reports
    >
    > The actual area of the Pivot Table in my worksheet is unlocked. Whe I select
    > the pivot table in the protected sheet I cannont refresh, but I can format
    > and create charts.
    >
    > Any help would be appreciated.
    >
    > Mike



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  3. #3
    mikeb
    Guest

    Re: Pivot Table Protection

    Code?

    I am setting up an estimating spreadsheet for out salespersons. I have
    several sheets that feed data to a "working" sheet. The salepersons fill out
    the remaining and in one instance I have used a pivot table to consolidate
    and sort the data.

    Excel Help tells me I should be able to "protect" the worksheet and allow
    the pivot table to be refreshed by another user. I do this by choosing "Use
    Pivot Table report" when I protect the sheet. This does not work. I have
    tried setting up a new pivot table in a blank workbook and get the same
    result. The "refresh" icon is ghosted in the protected sheets.

    Is this a glitch, or is it me?

    I'd like to keep this as simple as possible, as I am not the end user of the
    spreadsheet. Do you mean to record a macro that unprotects, refreshes, and
    protects the sheet? Can I link that code to a cell in the sheet so that when
    my salespersons get to the pivot table they can hit one button to do it all?

    Mike

    "Debra Dalgleish" wrote:

    > You could record code as you unprotect the sheet, refresh the pivot
    > table, and reprotect the sheet.
    >
    > Then, run that code as required, e.g. after you've updated the source
    > data, or when the pivot sheet is activated.
    >
    > mikeb wrote:
    > > Pivot Tables in my workbooks cannot be refreshed if the sheet is protected.
    > >
    > > Here's what I've done:
    > >
    > > Set up my worksheet.
    > > Formatted cells that I wish to be locked.
    > > Selected Tools>Protect Workbook
    > > Selected Tools>Protect Worksheet
    > > Clicked the following checkboxes: Select unlocked cells, Use Pivot Table
    > > reports
    > >
    > > The actual area of the Pivot Table in my worksheet is unlocked. Whe I select
    > > the pivot table in the protected sheet I cannont refresh, but I can format
    > > and create charts.
    > >
    > > Any help would be appreciated.
    > >
    > > Mike

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >
    >


  4. #4
    Debra Dalgleish
    Guest

    Re: Pivot Table Protection

    Do you remember where you found that information in Excel's Help?

    Yes, you can record a macro as you unprotect the sheet, refresh, then
    reprotect. In the recorded code, you can add a password, e.g.:

    '==========================
    Sub RefreshPivot()

    ActiveSheet.Unprotect Password:="MyPwd"
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    ActiveSheet.Protect Password:="MyPwd", _
    DrawingObjects:=True, Contents:=True, _
    Scenarios:=True, AllowUsingPivotTables:=True
    End Sub
    '=======================

    Then, add a "Refresh" button to the worksheet, and assign that macro to
    the button.

    mikeb wrote:
    > Code?
    >
    > I am setting up an estimating spreadsheet for out salespersons. I have
    > several sheets that feed data to a "working" sheet. The salepersons fill out
    > the remaining and in one instance I have used a pivot table to consolidate
    > and sort the data.
    >
    > Excel Help tells me I should be able to "protect" the worksheet and allow
    > the pivot table to be refreshed by another user. I do this by choosing "Use
    > Pivot Table report" when I protect the sheet. This does not work. I have
    > tried setting up a new pivot table in a blank workbook and get the same
    > result. The "refresh" icon is ghosted in the protected sheets.
    >
    > Is this a glitch, or is it me?
    >
    > I'd like to keep this as simple as possible, as I am not the end user of the
    > spreadsheet. Do you mean to record a macro that unprotects, refreshes, and
    > protects the sheet? Can I link that code to a cell in the sheet so that when
    > my salespersons get to the pivot table they can hit one button to do it all?
    >
    > Mike
    >
    > "Debra Dalgleish" wrote:
    >
    >
    >>You could record code as you unprotect the sheet, refresh the pivot
    >>table, and reprotect the sheet.
    >>
    >>Then, run that code as required, e.g. after you've updated the source
    >>data, or when the pivot sheet is activated.
    >>
    >>mikeb wrote:
    >>
    >>>Pivot Tables in my workbooks cannot be refreshed if the sheet is protected.
    >>>
    >>>Here's what I've done:
    >>>
    >>>Set up my worksheet.
    >>>Formatted cells that I wish to be locked.
    >>>Selected Tools>Protect Workbook
    >>>Selected Tools>Protect Worksheet
    >>>Clicked the following checkboxes: Select unlocked cells, Use Pivot Table
    >>>reports
    >>>
    >>>The actual area of the Pivot Table in my worksheet is unlocked. Whe I select
    >>>the pivot table in the protected sheet I cannont refresh, but I can format
    >>>and create charts.
    >>>
    >>>Any help would be appreciated.
    >>>
    >>>Mike

    >>
    >>
    >>--
    >>Debra Dalgleish
    >>Excel FAQ, Tips & Book List
    >>http://www.contextures.com/tiptech.html
    >>
    >>

    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1