Class TreeviewControl (Excel VBA)

The class TreeviewControl represents the hierarchical member-selection control of a cube field. To use a TreeviewControl class variable it first needs to be instantiated, for example

Dim tcl as TreeviewControl
Set tcl = ActiveCell.PivotField.CubeField.TreeviewControl

Drilled

Sets the "drilled" (expanded or visible) status of the cube field members in the hierarchical member-selection control of a cube field. This property is used primarily for macro recording and isn't intended for any other use.

The Drilled property accepts an array. Each element of the array corresponds to a level of the cube field that has been expanded. The maximum number of elements is the number of levels in the cube field. Each element of the array is an array of type String, containing unique member names that are visible (expanded) at the corresponding level of the control. To determine when members are explicitly hidden in an expanded view, see the Hidden property of the TreeviewControl object.

ActiveSheet.PivotTables("PivotTable1").CubeFields(1) _ 
 .TreeviewControl.Drilled = _ 
 Array(Array("", "", "", "", "", "", "", "", _ 
 "", "", "", ""), _ 
 Array("[state].[states].[AB]", _ 
 "[state].[states].[CA]", _ 
 "[state].[states].[IN]", _ 
 "[state].[states].[KS]", _ 
 "[state].[states].[KY]", _ 
 "[state].[states].[MD]", _ 
 "[state].[states].[MI]", _ 
 "[state].[states].[OH]", _ 
 "[state].[states].[OR]", _ 
 "[state].[states].[TN]", _ 
 "[state].[states].[UT]", _ 
 "[state].[states].[WA]"))

Hidden

Returns or sets a Variant value that represents the hidden status of the cube field members in the hierarchical member-selection control of a cube field.

Don't confuse this property with the FormulaHidden property. The Hidden property returns or sets an array. Each element of the array corresponds to a level of the cube field that is hidden. The maximum number of elements is the number of levels in the cube field. Each element of the array is an array of type String, which contains unique member names that are hidden at the corresponding level of the control. To determine when members are visible (expanded) in the control, see the DrilledDown property of the PivotItem object.

ActiveCell.PivotField.CubeField.TreeviewControl.Hidden = True