The Worksheet Object

The Worksheet object falls just under the Workbook object in Excel's object hierarchy. To investigate some of the events of the Worksheet object, the following code has been added to the SelectionChange() event procedure of Sheetl in the Center.xls workbook.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim msgOutput As String msgOutput = "The name of this worksheet is " & Worksheets(1).Name MsgBox (msgOutput) Worksheets(2).Select End Sub

The SelectionChange() event procedure was first introduced in Chapter 2, and is found in the object module of a worksheet. The SelectionChange() event procedure is triggered whenever the user changes the current selection in the worksheet. The Target argument passed to the SelectionChange() event procedure is a range that represents the cells selected by the user. I will discuss the Range object shortly; for right now, ignore it because the current example does not use the passed argument.

The code in the SelectionChange() event procedure is straightforward. First, a string variable is created and assigned a value ("The name of this worksheet is") that is then concatenated with the name of the worksheet obtained from the Name property of the Worksheet object. The full object path is not used to return the name of the worksheet, as this code will only be executed when the user changes the selection in the first worksheet of the Worksheets collection object (Sheetl). Therefore, the object path travels through the current Workbook object.

This is why index numbers can be used with the Worksheets property of the Workbook object without having to worry about returning the wrong sheet. After displaying the concatenated string in a message box, the Select() method of the Worksheet object is used to select the second worksheet in the Worksheets collection object. (This will generate an error if only one worksheet exists in the collection.)

Next, code is added to the Worksheet_Activate() event procedure of Sheet2. The Worksheet _Activate() event procedure is triggered when a worksheet is first selected by the user or, in this case, by selecting the worksheet using program code (Worksheets(2).Select). The code is essentially the same as the previous example.

Private Sub Worksheet_Activate() Dim msgOutput As String msgOutput = "This worksheet is " & Worksheets(2).Name MsgBox (msgOutput)

The Worksheet_Activate() event procedure is not triggered when a workbook is first opened, so it is not a good place for initialization routines intended to run as soon as a workbook is opened. These procedures should be placed in the Workbook_Open() event procedure.

You may have noticed in the object browser an object called Sheets. The Sheets collection object is nearly identical to the Worksheets collection object and the two objects can often be used interchangeably (as is the case in the previous two examples). The difference between these two objects is that the Sheets collection object will also contain any chart sheets open in the active workbook. So, if you expect chart sheets to be open in the workbook of interest, you should access worksheets using the Sheets collection object; otherwise, either collection object will suffice.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment