Another important concept in VBA is that objects can respond to events. A mouse click on a command button, a double-click on a cell, a recalculation of a worksheet, and the opening and closing of a workbook are examples of events.

All of the ActiveX controls can respond to events. These controls can be embedded in worksheets and in UserForms to enhance the functionality of those objects. Worksheets and workbooks can also respond to a wide range of events. If you want an object to respond to an event, enter VBA code into the appropriate event procedure for that object. The event procedure resides in the code module behind the Workbook, Worksheet, or UserForm object concerned.

For example, you might want to detect that a user has selected a new cell and highlight the cell's complete row and column. You can do this by entering code in the Worksheet_SelectionChange() event procedure:

1. First activate the VBE window and double-click the worksheet in the Project Explorer.

2. From the drop-down lists at the top of the worksheet code module, choose Worksheet and SelectionChange, and enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Rows.Interior.Colorlndex = xlColorlndexNone Target.EntireColumn.Interior.Colorlndex = 36 Target.EntireRow.Interior.Colorlndex = 36 End Sub

This event procedure runs every time the user selects a new cell, or block of cells. The parameter, Target, refers to the selected range as a Range object. The first statement sets the Colorlndex property of all the worksheets cells to no color, to remove any existing background color. The second and third statements set the entire columns and entire rows that intersect with the selected cells to a background color of pale yellow. This color can be different, depending on the color palette set up in your workbook.

The use of properties in this example is more complex than you have seen before. Now analyze the component parts. If you assume that Target is a Range object referring to cell B10, then the following code uses the EntireColumn property of the B10 Range object to refer to the entire B column, which is the range B1:B1048576, or B:B for short:

Target.EntireColumn.Interior.Colorlndex = 3 6

Similarly, the next line of code changes the color of row 10, which is the range A10:XFD10, or 10:10 for short:

Target.EntireRow.Interior.Colorlndex = 3 6

The Interior property of a Range object refers to an Interior object, which is the background of a range. Finally, set the ColorIndex property of the Interior object equal to the index number for the required color.

This code might appear to many to be far from intuitive. So how do you go about figuring out how to carry out a task involving an Excel object?

0 0

Post a comment