Prev: VBA using VLookUp
Next: 57 KewaSa.com DDL Torrents
From: Lee on 13 Oct 2009 15:21
Below is a sample of a pivot table that I'm trying to restrict. Found code
on the web that allows me to do it but I'm having a problem

Sub h_PT_Restrict_PivotTable()
'Restrict pivot table functionality
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False 'Excel 2002+
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
For Each pf In .PivotFields
'If pf.Value = "Data" Then End
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With
End Sub

This works fine until it hits "Data". I can put a watch on it and see it
run through all of the pivot fields. It will run through each field coming
to "Data" last. When the pf value becomes "Data" the macro stops on
..DragToPage = False with an application defined or object defined error.

Any ideas ? I put an If statement (commented out above) that when the pf
value = "Data" then end. This works fine as long as the user doesn't change
"Data" to anything else, if they do then it's back to the same problem.
Should it read "Data" as a pivot field?

Data
TASK ID DESCRIPTION Labor Cost Other Cost
1.1 Base Year Labor $ 131,596 $ 9,527
2.1 Option Period 1 Labor $ 542,806 $ 39,296
3.1 Option Period 2 Labor $ 575,364 $ 41,653
4.1 Option Period 3 Labor $ 610,398 $ 44,189
5.1 Option Period 4 Lalbor $ 647,571 $ 46,880
Grand Total $2,507,735 $ 181,545

--
Lee
From: GB on 15 Oct 2009 10:59
If I understand your situation correctly, the error occurs when you try to
modify the .DragToPage "property" of the pivotfield that is in essence
contains your data. I did a quick review of the pivotfields and pivotfield
collection object, and it looks like you could instead of going through each
pivotfield as an entire collection, you could go through each type of
pivotfield that is in the pivot table. This would allow you to omit
performing the text comparison of pf.value = "Data" and coming across the
error that is stopping your code.

An alternate method, though I didn't see exactly how to accomplish it is to
check for the type of the pivotfield that is being evaluated. It would
appear that to do this you would have to use the same fields that would be
used above, so might as well do that from the start. :)

So you could loop through each columnfield, datafield, hiddenfield,
pagefield, rowfield, and/or visiblefield as necessary instead of just
Pivotfields. That way if the attribute "Data" is a member of the datafields
then it would be omitted by going through all of the other field types.

It would be the same setup as you have for the most part, but instead of for
each pf in .pivotfields it would be the respective .datafields,
..columnfields, .hiddenfields, etc.. then the next "group", then the next
"group" until you are done.

This way it would not matter whether the user changes the name "Data" or
not, but would limit based on the type of information being addressed.

"Lee" wrote:

> Below is a sample of a pivot table that I'm trying to restrict. Found code
> on the web that allows me to do it but I'm having a problem
>
> Sub h_PT_Restrict_PivotTable()
> 'Restrict pivot table functionality
> Dim pf As PivotField
> With ActiveSheet.PivotTables(1)
> .EnableWizard = False
> .EnableDrilldown = False
> .EnableFieldList = False 'Excel 2002+
> .EnableFieldDialog = False
> .PivotCache.EnableRefresh = False
> For Each pf In .PivotFields
> 'If pf.Value = "Data" Then End
> With pf
> .DragToPage = False
> .DragToRow = False
> .DragToColumn = False
> .DragToData = False
> .DragToHide = False
> End With
> Next pf
> End With
> End Sub
>
> This works fine until it hits "Data". I can put a watch on it and see it
> run through all of the pivot fields. It will run through each field coming
> to "Data" last. When the pf value becomes "Data" the macro stops on
> .DragToPage = False with an application defined or object defined error.
>
> Any ideas ? I put an If statement (commented out above) that when the pf
> value = "Data" then end. This works fine as long as the user doesn't change
> "Data" to anything else, if they do then it's back to the same problem.
> Should it read "Data" as a pivot field?
>
> Data
> TASK ID DESCRIPTION Labor Cost Other Cost
> 1.1 Base Year Labor $ 131,596 $ 9,527
> 2.1 Option Period 1 Labor $ 542,806 $ 39,296
> 3.1 Option Period 2 Labor $ 575,364 $ 41,653
> 4.1 Option Period 3 Labor $ 610,398 $ 44,189
> 5.1 Option Period 4 Lalbor $ 647,571 $ 46,880
> Grand Total $2,507,735 $ 181,545
>
> --
> Lee
From: GB on 15 Oct 2009 11:21
I thought I had responded to this earlier, but didn't see that my response
made it to the site. :\

Instead of going through every pivot field, I would recommend looking up the
help info on the pivotfields object while in VBA. You should note that there
is a datafields group (or similar). If in fact you are trying to address
each type of field and restrict certain actions, then you could go through
each type of field and apply only the actions applicable to that field type.
It would seem that the word "data" applies to your datafield, and therefore
because there is no .dragtopage for the datafield, you are experiencing the
problem that you are and in the hard place that you have found yourself. :)

This way you can go through each type of field one after another and lock
down what you are wanting, assuming that the user isn't able to modify or
stop the code and be mischevious. :}

"Lee" wrote:

> Below is a sample of a pivot table that I'm trying to restrict. Found code
> on the web that allows me to do it but I'm having a problem
>
> Sub h_PT_Restrict_PivotTable()
> 'Restrict pivot table functionality
> Dim pf As PivotField
> With ActiveSheet.PivotTables(1)
> .EnableWizard = False
> .EnableDrilldown = False
> .EnableFieldList = False 'Excel 2002+
> .EnableFieldDialog = False
> .PivotCache.EnableRefresh = False
> For Each pf In .PivotFields
> 'If pf.Value = "Data" Then End
> With pf
> .DragToPage = False
> .DragToRow = False
> .DragToColumn = False
> .DragToData = False
> .DragToHide = False
> End With
> Next pf
> End With
> End Sub
>
> This works fine until it hits "Data". I can put a watch on it and see it
> run through all of the pivot fields. It will run through each field coming
> to "Data" last. When the pf value becomes "Data" the macro stops on
> .DragToPage = False with an application defined or object defined error.
>
> Any ideas ? I put an If statement (commented out above) that when the pf
> value = "Data" then end. This works fine as long as the user doesn't change
> "Data" to anything else, if they do then it's back to the same problem.
> Should it read "Data" as a pivot field?
>
> Data
> TASK ID DESCRIPTION Labor Cost Other Cost
> 1.1 Base Year Labor $ 131,596 $ 9,527
> 2.1 Option Period 1 Labor $ 542,806 $ 39,296
> 3.1 Option Period 2 Labor $ 575,364 $ 41,653
> 4.1 Option Period 3 Labor $ 610,398 $ 44,189
> 5.1 Option Period 4 Lalbor $ 647,571 $ 46,880
> Grand Total $2,507,735 $ 181,545
>
> --
> Lee
 | 
Pages: 1
Prev: VBA using VLookUp
Next: 57 KewaSa.com DDL Torrents