Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Allowing use of Pivot Tables; protected sheets (Excel 2003)

    Hi all....I need some help with a macro...I wish to protect a worksheet, but still permit people to use Pivot Tables on that sheet. Presently, I have the following as a macro that will protect/unprotect, reformat the sheet in question:
    Sub AF_WEEKLY()
    '
    ' AF_WEEKLY Macro
    ' Macro recorded 2/23/2006 by David J. McNab
    '

    '
    ActiveSheet.Unprotect Password:="open"
    Cells.Select
    Selection.Columns.AutoFit
    Cells.Select
    Selection.Rows.AutoFit
    Rows("104:152").Select
    Selection.EntireRow.Hidden = True
    Range("B1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, AllowUsingPivotTables:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, Password:="open"
    End Sub

    Even thought I have "AllowUsingPivotTables:=True", Data|Pivot Table and Pivot Chart Report is not available (it is 'grayed' out).....have I used the wrong 'command' in my VBA code/macro? Thank you.

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

    Re: Allowing use of Pivot Tables; protected sheets (Excel 2003)

    AllowUsingPivotTables:=True means that it is allowed to use existing pivot tables - users can drag the fields around, add and remove fields etc. But they cannot create new pivot tables, as you have found. If you want to offer that capability, you'd have to write code to unprotect the sheet, create a new pivot table, then protect the sheet again.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Allowing use of Pivot Tables; protected sheets

    ...do you mean a macro that simply Unprotects the worksheet, and then a Pivot Table can be created, following which the sheet is manually re-protected?....I can't see anything in the sequence of Tools|Protection|Protect Sheet dialogue box that permits someone to 'create' a Pivot Table......and don't know enough VBA (yet..) to know how to select the code steps in VBA to accomplish this...

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

    Re: Allowing use of Pivot Tables; protected sheets

    The Protect Sheet dialog doesn't deal with creating pivot tables.
    You'd have to write code to create a pivot table, asking the user for input, or you could display the wizard:

    Application.Dialogs(xlDialogPivotTableWizard).Show

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Allowing use of Pivot Tables; protected sheets

    ...very nice, Hans.....I added that code as a macro in the worksheet, and will test it over the next day or so.....I left the "UsingPivotTablesAllowed:=True" as part of the macro on the w/sheet in question, assuming that will permit someone to use (or manipulate) a P-Table once it's created..??...thanks (as always) for your help and you time (and willingness to lead the way for the rest of us.,.).

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

    Re: Allowing use of Pivot Tables; protected sheets

    Yes, having UsingPivotTablesAllowed:=True ensures that the user will be able to manipulate the pivot table created by yhe Wizard when the sheet has been protected again.

Posting Permissions

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