Microsoft Office Tutorials and References
In Depth Information
Part 5: Manipulating Excel Objects
Creating Advanced User Forms
MsgBox "Illegal row number"
If r > 1 And r <= LastRow Then
CustomerId.Text = FormatNumber(Cells(r, 1), 0)
CustomerName.Text = Cells(r, 2)
City.Text = Cells(r, 3)
State.Text = Cells(r, 4)
Zip.Text = Cells(r, 5)
DateAdded.Text = FormatDateTime(Cells(r, 6), vbShortDate)
MsgBox "Invalid row number"
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 = ""
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.)