A PivotItem is a unique value of a PivotField. To illustrate, consider the following code:
Dim pi As PivotItem
For Each pi In _
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store City").PivotItems
Debug.Print pi.Name
NextThat code will print the list:
BOSTON LOS ANGELES NEW YORK
which contains the distinct Store City values from the Store City pivot field.
The PivotItems method of the PivotField object returns PivotItem objects. The syntax:
PivotFieldObject.PivotItemsreturns the collection of all PivotItem objects for that PivotField. The syntax:
PivotFieldObject.PivotItems(Index)
can return a single PivotItem object or an array of PivotItem objects
(by setting Index to an array of indexes).
Table 20-5 shows the properties and methods of the PivotItem object. Let us take a look at some of these members. Note that several of the members of the PivotField object also apply to the PivotItem object.
This property returns a Range object representing the data area associated with the given PivotItem. To illustrate, the code:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Company").DataRange.Selectresults in Figure 20-37.
The LabelRange property returns a Range object that represents the label cells for the PivotItem. Figure 20-38 illustrates the results of the code:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Company").LabelRange.Select
This property returns True if the pivot item is a
calculated item. We discuss calculated items later in the chapter.
The read-write Name property returns or sets the name of the PivotItem. This is the value that appears in the label for that item. The Value property is the same as the Name property.
The read-only SourceName property returns the name of the item in the original source data. This may differ from the value of the Name property if the Name property has been changed.
The Position property returns or sets the position of the pivot item. For instance, the code:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Franchise").Positionreturns the number 2, since Franchise is the second pivot item in the pivot table (see Figure 20-10). Moreover, we can reverse the positions of Company and Franchise by setting the Position of the Franchise pivot item to 1, as follows:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Franchise").Position = 1This read-only property returns the number of records in the PivotTable cache that contain the pivot item. For instance, the code:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Franchise").RecordCountwill return the number 80 because there are 80 rows in the source table (and, hence, the pivot cache) that involve the Franchise store type.
When this read-write property is set to
True, the pivot item is shown in detail; if it is
False, the PivotItem is hidden. To illustrate,
consider, as usual, the pivot table in Figure 20-10.
The code:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store City"). _
PivotItems("Boston").ShowDetail = Falseresults in the pivot table in Figure 20-39. As we can see, the Transactions and Sales for Boston are summed over all (both) store types (Company and Franchise).
Unfortunately, there seems to be a problem when the ShowDetail method is applied to inner pivot items. For instance, the code:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Company").ShowDetail = Falsedoes seem to set the ShowDetail property to False,
as can be verified by the code:
MsgBox ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type"). _
PivotItems("Company").ShowDetailHowever, 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 WithThe 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 = FalseThe 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.ShowDetailNote 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.