pivot table vb code - how do I restrict access to dropdown box?

[Kevin Gallagher]

If you have a second, can you help a try-hard programmer……..

i have some of what I need (see below), However I can’t do one las
thing….can anyone recommend any code that would prevent the user fro
selecting any of the dropdown boxes (e.g. unable to use the filter) o
the pivot table.

note: I still need to allow them to drilldown to the detail on th
selections provided if required…..


I imagine it would be something like pf.?????? = false
I just don’t know what the ???? bit is.
Know any good sites for this type of question?


Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MySecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True
Password:="MySecretWord"
Next

Dim pf As PivotField

With Worksheets("Pivot").PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.PivotCache.EnableRefresh = True
For Each pf In .PivotFields
pf.DragToPage = False
pf.DragToRow = False
pf.DragToColumn = False
pf.DragToData = False
pf.DragToHide = False

Next pf
End With

End Sub



All help appreciated
Cheers
Kevin Gallaghe

 

[Debra Dalgleish]

The following code will disable selection:

'========================
Sub DisableSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next
End Sub
'==================

There are links to site with PivotTable information here:

http://www.peltiertech.com/Excel/Pivots/pivotlinks.htm

 

[Kevin Gallagher]

Thanks again for the help Debra

My next problem is that any new Selections (things change over time
will automatically appear on all of the pivot tables.

Is there any way to restrict unselected fields from appearing?

The default is for new items to be selected within the filters - I nee
for the default to be that any new items remain unselected.

Once again - all help would be very much appreciated

Cheers
Kevin.
ps. FYI: I am a systems accountant and I am trying to put together
cost centre report. The data needs to be split amongst 20 or so lin
managers - who are not allowed to view each others reports...(the cos
centres they can view remain fixed, however occasionaly new cos
centres may be created)..The data is required at a summary level - bu
each manager also needs to drill down to the detail if required.

...with the requirement that it is user-friendly...
I am planning on creating the 20 pivot tables from one central list o
data -
the filters will not be enabled (thanks Debra) however the users wil
be able to drill down.
Any new cost centre would neccessitate that affected pivot tables ar
revised (but hopefully not all)

The idea would be to set it up once - and refresh the pivot tables onc
per month with new cost centre data

All tips would be appreciated

 

[Debra Dalgleish]

To prevent managers from viewing each other's reports, you could use an
Advanced Filter to extract each person's data, and create a fully
functional pivot table based on that.

With a macro, it shouldn't be any more work than what you're currently
doing, and would alleviate the security problems.