## Children of the Worksheet Object

Let us discuss a few of the children of the Worksheet object. Others will be discussed in later chapters.

The Comments property returns the Comments collection, which consists of all Comment objects (comments) in the worksheet. We will discuss the Comment object in Chapter 19.

The Names collection

We discussed the Names collection and Name objects in Chapter 16, and so we refer the reader to that earlier discussion.

The Outline object

To illustrate Excel outlining using code, consider the worksheet shown in Figure 18-3. Our goal is to produce the outline in Figure 18-4.

Figure 18-3. Illustrating Excel outlines

 A B C D 1 Con Col2 Col3 2 Rcwl 73 33 23 53 R&V2 123 22 222 4 Row3 23 ; 34 345 3d SubTotaM 432 89 590 6 7 Rcw4 223 23 454 8 RChVÖ 345 10 53 9 Row6 11 13 4 10 SübTotaS2 579 46 511 11 12 Total 1011 135 1101

Figure 18-4. The end result

 1 | 2 3 | A B C D 1 CoM Col? Col 3 2 Row1 78 33 23 3 Rqw2 123 22 277 4 Row3 231 34 345 - 5 SubTctan 432 99 590 ■ 6 » 7 Row4 223 454 3 Row5 345 10 53 9 RowG 11 13 4 10 SubTotal2 573 46 511 - 11 12 Total 1011 135 1101

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

.Rows("2:4").Group .Rows("7:9").Group .Rows("2:11").Group 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.

The same thing can be accomplished using the ShowLevels method of the Outline object, whose syntax is:

OutlineObject.ShowLevels(.owLevels, ColumnLevels) For instance, the code:

ActiveSheet.Outline.ShowLevels 2

is equivalent to clicking on the button labeled 2 and has the effect of showing all levels above and including level 2, as pictured in Figure 18-5.

Figure 18-5. Outline collapsed to level 2

 1 2 3 A B C D 1 CoM CoE2 Col 3 + 5 Sublet all 432 89 590 6 + 10 SubTctal2 579 46 511 11 12 Total 1011 136 1101

The PageSetup object

The PageSetup object represents the page formatting (such as margins and paper size) of an Excel worksheet. Each of the page-formatting options is set by setting a corresponding property of the PageSetup object.

The PageSetup property of the Worksheet object returns the worksheet's PageSetup object.

The properties and methods of the PageSetup object are shown in Table 18-3. (All of the items in Table 18-3 are properties except the PrintQuality method.) Most of the members in Table 18-3 are self-explanatory (and hold no real surprises), so we will not discuss them.

To illustrate, the following code sets some of the properties of the active worksheet:

To illustrate, the following code sets some of the properties of the active worksheet:

### With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(1) .RightMargin = Application.InchesToPoints(1) .PrintTitleRows = "A1" .PaperSize = xlPaperLetter End With

Note the use of the InchesToPoints function, which is required if we want to express units in inches, since most of the formatting properties require measurement in points. Referring to Figure 18-6, the PrintTitleRows property will cause the word Report, which lies in cell A1, to appear on each page of the printout.

Figure 18-6. A worksheet and the PrintTitleRows property

Figure 18-6. A worksheet and the PrintTitleRows property

0 0