The Window Object

In VBA, if you want to detect what sheets are currently grouped, you use the SelectedSheets property of the Window object. You might think that SelectedSheets should be a property of the Workbook object, but that is not the case. SelectedSheets is a property of the Window object, because you can open many windows on the same workbook and each window can have different groups, as Figure 3-5 shows.

-ft J ï Micr.jjQft Excel

_ m

HamE

Inrert PagE Layout Formulas

Data

BEVÍBW vieiW

DíveiopEr

B5

f* \ =B3-B4

if

A

B I C 1 D

E

F

G ! H

I

1 Í

2

Apples Mangoes Bananas

Lychees

3 Production

907 956 937

961

i Sales

964 947 915

915

5 1

-57 9 22

46

fi&H » jan 2G07 Feb 2007 Mar 2007

Apr 2007

<3

'D Slieetlcip' ^lsmii (Orcuo)

. -

--

A 1

B ! C D

E

F

_ a H i

£

1

2

Apples Mangoes Bananas

Lychees

3 Production

4 Sales

S

01 0 0

0

h « 1 S Jan 2007 ] Feb 20D7 Mar 2007

Apr 2007

im

_ju •

Ready £3

¡B

a SlLâï?« '--I-—^rr

Figure 3-5

There are many other common workbook and worksheet properties that you might presume to be properties of the Workbook object or the Worksheet object, but which are actually Window object properties. Some examples of these are ActiveCell, DisplayFormulas, DisplayGridlines, DisplayHeadings, and Selection. See the Window object in Appendix A for a full list.

The following code determines which cells are selected on the active sheet, makes them bold, and then goes on to apply bold format to the corresponding ranges on the other sheets in the group:

Sub FormatSelectedGroup() Dim sht As Object Dim sRangeAddress As String sRangeAddress = Selection.Address

For Each sht In ActiveWindow.SelectedSheets If TypeName(sht) = "Worksheet" Then sht.Range(sRangeAddress).Font.Bold = True End If Next sht

End Sub

The address of the selected range on the active sheet is captured in sRangeAddress as a string. It is possible to activate only the selected sheets and apply bold format to the selected cells. Group mode ensures that the selections are the same on each worksheet. However, activating sheets is a slow process. By capturing the selection address as a string, you can generate references to the same range on other sheets using the Range property of the other sheets. The address is stored as a string in the form "$B$2:$E$2,$A$3:$A$4", for example, and need not be a single contiguous block.

FormatSelectedGroup allows for the possibility that the user can include a chart sheet or another type of sheet in the group of sheets. It checks that the TypeName of the sheet is indeed "Worksheet" before applying the new format.

It is necessary to declare sht as the generic Object type if you want to allow it to refer to different sheet types. There is a Sheets collection in the Excel object model, but there is no Sheet object.

0 0

Post a comment