Page 449 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Creating Advanced User Forms
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If

If r > 1 And r <= LastRow Then
CustomerId.Text = FormatNumber(Cells(r, 1), 0)
CustomerName.Text = Cells(r, 2)
City.Text = Cells(r, 3)
Chapter 20
State.Text = Cells(r, 4)
Zip.Text = Cells(r, 5)
DateAdded.Text = FormatDateTime(Cells(r, 6), vbShortDate)
DisableSave
ElseIfr=1 Then
ClearData
Else
ClearData
MsgBox "Invalid row number"
End If

End Sub
Knowing that RowNumber contains a numeric value, the CLng function is used to convert the
value in RowNumber into the variable r. The rest of the code merely uses r to extract the
information from the proper row and copy it to the correct field. Otherwise, a message box
will be displayed to the user indicating that the row number value is invalid. The ClearData
routine simply assigns an empty string to each field on the form to clear out any values that
might have already been displayed on the form. (Remember that the ComboBox control can’t
be set to an empty string and should be set to a valid state value.)

Private Sub ClearData()
CustomerId.Text = ""
CustomerName.Text = ""
City.Text = ""
State.Text = "AK"
Zip.Text = ""
DateAdded.Text = ""
End Sub
Simply because the row number is numeric doesn’t mean that it’s safe to pass the number to
the Cells method. You can add the constant LastRow to the start of the user form module like
this while testing this routine. (Later in this chapter, you’ll see how to determine the real last
row of data in the worksheet, and you’ll convert this constant to a module level variable.)

423