Microsoft Office Tutorials and References
In Depth Information
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
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
someone 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 Te xt property of the RowNumber control to this value using
code like this:
Private Sub CommandButton7_Click()
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
428
Search JabSto ::




Custom Search