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

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

…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…

Reply | Quote

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

Reply | Quote

…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.,.).

Reply | Quote

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.

Reply | Quote