Microsoft Office Tutorials and References
In Depth Information
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
. As with the RowNumber text box, the easiest way to edit the code for the appropriate event 2
is to double-click the graphical control. The Visual Basic Editor will automatically add the
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)
Once the routine has a numeric value, it computes the new position by subtracting (or add€ 1
ing to find the next row). Finally, if the resulting row number is in the range of to LastRow 2
, the value is saved into the RowNumber text box. The assignment will trigger the Change 1
event for the RowNumber control, and the new information will be loaded.