Microsoft Office Tutorials and References
In Depth Information
20.9.8 Visible Property
However, the pivot table does not reflect this change! (At least this happens on the two systems on
which I have run this code. You should check this carefully on any system on which you intend to
run this code. A similar problem occurs with the Subtotals property as well.)
As another example, the following code toggles the display of details for the Boston pivot item:
With ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store City").PivotItems("Boston")
.ShowDetail = Not .ShowDetail
End With
The ShowDetail property also applies to the Range object, even when the range lies inside a pivot
table. To illustrate, the following code will also produce the pivot table in Figure 20-39 :
ActiveSheet.PivotTables("Sales&Trans"). _
PivotSelect "Boston", xlDataAndLabel
Selection.ShowDetail = False
The following code toggles the display of the Boston details:
Dim rng As Range
ActiveSheet.PivotTables("Sales&Trans"). _
PivotSelect "Boston", xlDataAndLabel
Set rng = Selection.Cells(1, 1)
rng.ShowDetail = Not rng.ShowDetail
Note that rng refers only to the first cell in the range representing the Boston data and labels; that
is, we have:
Set rng = Selection.Cells(1, 1)
This is done because, when applied to the Range object, the ShowDetail property will return a
value only when the range is a single cell.
As another illustration, the following code will hide the details for the entire Store City pivot field:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotSelect "'Store City'", xlDataAndLabel
Selection.ShowDetail = False
ActiveSheet.PivotTables("Sales&Trans"). _
PivotSelect "'Store City'", xlDataAndLabel
(Replacing False by True will unhide the details.)
We must conclude by suggesting that since the ShowDetails property is very poorly documented,
you should experiment carefully and completely before relying on this property to perform in a
certain way.
20.9.8 Visible Property
This property determines whether or not the pivot item is visible in the pivot table.
20.10 PivotCell and PivotItemList Objects
 
Search JabSto ::




Custom Search