Page 454 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Microsoft Office Excel 2003 Programming Inside Out
Private Sub PutData()
Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If

If r >1Andr<LastRow Then
Cells(r, 1) = CustomerId.Text
Cells(r, 2) = CustomerName.Text
Chapter 20
Cells(r, 3) = City.Text
Cells(r, 4) = State.Text
Cells(r, 5) = Zip.Text
Cells(r, 6) = DateAdded.Text
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub

The error checking isn’t absolutely necessary, but it probably is a good idea just in case some-
one put an invalid value in the RowNumber text box, jumped to another application, and
then came back. In that scenario, it’s possible to enter a different value in the RowNumber
text box without retrieving any data.
Notice that after the data is saved to the worksheet, DisableSave routine is called. This is
necessary because the data on the user form now represents the same data stored on the
worksheet.

Adding Data

Pressing the Add button calls the CommandButton7_Click event, which displays the first
blank row at the end of the worksheet. Because the LastRow variable points to this row, it’s
merely a matter of setting the Text property of the RowNumber control to this value using
code like this:

Private Sub CommandButton7_Click()
RowNumber.Text = FormatNumber(LastRow, 0)

End Sub

428