Listing Using the Change Event to Respond to Worksheet Changes

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$B$1"

ChangeColumnWidth Target.Value Case "$B$2"

ChangeRowHeight Target.Value End Select End Sub

Private Sub ChangeColumnWidth(Width As Variant) If IsNumeric(Width) Then

Me.Columns.ColumnWidth = Width ElseIf Width = 0 Then

Me.Columns.ColumnWidth = Me.StandardWidth End If End If End Sub

Private Sub ChangeRowHeight(Height As Variant) If IsNumeric(Height) Then

If Height > 0 And Height < 100 Then

Me.Rows.RowHeight = Height ElseIf Height = 0 Then

Me.Rows.RowHeight = Me.StandardHeight End If End If

End Sub

Listing 7.10 changes the width of all of the columns or the height of all of the rows on the worksheet depending on which cell you change. Whenever a change is made to the worksheet, the Worksheet_Change procedure executes. The reason the Change event is so powerful is that Excel passes a reference to the range that is changed. By looking at the address of the range, you can decide what you want to do using a Select Case statement. In the next two chapters, you'll see that you actually have a great number of options when it comes to using the range passed to the event procedure.

In order to keep the Worksheet_Change event procedure small and easy to maintain, it's a good idea to create other procedures that respond to the event. All you do with Worksheet_Change is determine which action to take and then delegate to the appropriate procedure. In Listing 7.10, you call either ChangeColumnWidth or ChangeRowHeight if one of the appropriate cells is changed.

To try this code, follow these steps:

1. In the VBE, select the worksheet you'd like to add the code to in the Project Explorer under Microsoft Excel Objects.

2. Right-click the worksheet and choose View Code.

3. Select Worksheet from the objects drop-down list.

4. Select Change from the Procedures/Events drop-down list.

5. Enter the code for the Worksheet_Change procedure.

6. Enter the code for the ChangeColumnWidth and ChangeRowHeight procedures.

7. Change the values in either cell B1 or B2 to change the column width or row height respectively.

One last comment on Listing 7.10—in the event that 0 (zero) is passed to either ChangeColumnWidth or ChangeRowHeight, the column width or row height will be set to the standard width or height.

0 0

Post a comment