Page 451 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Creating Advanced User Forms
Navigating The Worksheet

Clicking any of the four navigation buttons should automatically adjust the value in the
RowNumber text box. Then, because the value in RowNumber has been changed, the
RowNumber_Change event will be fired and the currently displayed row will be updated.
Each of the four buttons represents a slightly different situation. The code for the First button
is the simplest in that only a simple assignment statement is necessary to set RowNumber to
2. As with the RowNumber text box, the easiest way to edit the code for the appropriate event
is to double-click the graphical control. The Visual Basic Editor will automatically add the
Chapter 20
event, and you can enter this line of code to complete it.

RowNumber.Text = "2"

Tip Test As You Go
As you create the code for each button, take time to run the program and see the results.
One of the strengths of Visual Basic is that you can quickly test your programs. It’s far eas­
ier to debug 5 or 10 lines of code that you just added than to wait until you’ve added a few
hundred lines of code.

The Prev and Next buttons are a little more complicated because you need to compute the
value of the previous or next row based on the value of the current row. Like the GetData rou€
tine, this routine (shown in the following listing) begins by verifying that the value contained
in RowNumber is numeric.

Private Sub CommandButton2_Click()
Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r= r − 1
Ifr>1And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If

End If
End Sub

Once the routine has a numeric value, it computes the new position by subtracting 1 (or add€
ing 1 to find the next row). Finally, if the resulting row number is in the range of 2 to LastRow
−1, the value is saved into the RowNumber text box. The assignment will trigger the Change
event for the RowNumber control, and the new information will be loaded.

425