Microsoft Office Tutorials and References
In Depth Information
18.2 Children of the Worksheet Object
The first step in obtaining the outline in Figure 18-4 is to set the properties of the Outline
object for this worksheet. The Outline property of the Worksheet object returns an
Outline object, so we begin with:
With ActiveSheet.Outline
.SummaryRow = xlBelow
.AutomaticStyles = False
End With
Setting the SummaryRow property to xlBelow tells Excel that our summary rows (the
subtotal and total rows) lie below the detailed data. Thus, Excel will place the
expansion/contraction buttons (the small buttons displaying minus signs in Figure 18-4 ) at
the appropriate rows.
Setting AutomaticStyles to False prevents Excel from tampering with our formatting.
Otherwise, Excel would remove the boldfacing on the summary rows.
As you can see in Figure 18-4 , we want to make the following groupings:
Rows 2-4
Rows 7-9
Rows 2-11
For this, we use the Group method of the Range object. In particular, the following code
accomplishes the desired grouping, resulting in Figure 18-4 :
With ActiveSheet
End With
Note that the SummaryColumn property of the Outline object governs the location of the
expansion/contraction buttons when columns grouped.
To expand or collapse levels, the user can click the small numbered buttons at the top of
the leftmost column in Figure 18-4 . Clicking on button number X results in all levels
above X being completely expanded and all levels below and including X being
completely contracted. Thus, all rows at level X and above are made visible, but no levels
below X are visible.
Search JabSto ::

Custom Search