Page 450 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Microsoft Office Excel 2003 Programming Inside Out
Const LastRow = 20
Using this value, you can verify that the row number is always in the range of 2 to LastRow,
thus ensuring that the value in r always points to a valid row on the worksheet.
Remember that we have to handle the value of 1 as a special case because it’s possible that the
user has entered a 1 into the RowNumber text box as part of entering a number beginning
with 1, such as 12 or 123. The easiest way to handle this issue is to simply clear the form by
calling ClearData without issuing an error message.
Notice that the FormatNumber routine is used to convert the value in the first column to a
number rather than simply assigning the value directly to the text box control. This tech€
nique ensures that the value is properly formatted in the field.
The same argument applies to the date data from the sixth column. The FormatDateTime
Chapter 20
function ensures that the data is properly formatted. While the function isn’t specifically
needed, it serves to remind you that you aren’t dealing with text data.

Once the data is loaded onto the form, the Save and Cancel buttons are disabled by calling the
DisableSave routine. These buttons are enabled only when the user changes a piece of infor€
mation on the form.

Private Sub DisableSave()

CommandButton5.Enabled = False
CommandButton6.Enabled = False

End Sub
To hook the GetData routine into the form, switch from the code view of the user form to the
object view showing the graphical representation of the form. Double-clicking the RowNumber
control will take you back to the code view, but with one minor exception: the cursor will be
placed in the middle of a new routine named RowNumber_Change.

Inside the new event, add a call to the GetData routine. This means that any time the data in
the RowNumber control changes, the data shown in the form will be updated.

Private Sub RowNumber_Change()

GetData
End Sub
To test the routine, choose Run, Run Sub/UserForm from the main menu or press the F5 key.
Then enter a row number into the RowNumber control. You will notice that the data from the
appropriate row will be displayed. Also notice that it’s impossible to enter a bad value for the
row without generating an error message.

424