Page 315 - Excel Progamming Weekend Crash Course

r540629 ch22.qxd 9/2/03 9:35 AM Page 290

290 Sunday Morning

At the same time, the form needs code to clear all data from its controls. This is neces-
sary when the Next button is clicked, of course, but also when the Cancel or Done button is
clicked. Even though the form is hidden with the Hide method, it retains any data in its
controls the next time it is displayed. For this reason, the controls need to be cleared. This
is a simple matter of setting the Value property of each control to a blank string. This code
is placed in a procedure named ClearForm, as shown in Listing 22-4. Add this procedure to
the form now.

Listing 22-4 The ClearForm procedure erases all data from the form’s controls

Public Sub ClearForm()

‘ Clears all data from the form.
txtFirstName.Value = “”
txtLastName.Value = “”
txtAddress.Value = “”
txtCity.Value = “”
txtZip.Value = “”
cmbStates.Value = “”
End Sub

Entering the data into the worksheet requires that the program locate the first empty
data row. You know that the first column heading is in cell A2. This means that the first
blank row could start in cell A3 or any cell below it. There are several ways you could iden-
tify the first empty row. The one used here is as follows:

1. Start with cell A2 as a reference point.
2. Use the CurrentRegion property to get a range containing the header row plus all
existing data.
3. Use the Offset method to get a range offset by the number of rows in the original
range. This new range is one row below the original range and contains the six
cells in the first empty row.
4. Use the Cells property to access individual cells in this range to insert the data.
The code for the EnterDataInWorksheet procedure is shown in Listing 22-5. Add this
procedure to the user form using the techniques you have already learned.

Listing 22-5 The EnterDataInWorksheet procedure

Public Sub EnterDataInWorksheet()
‘ Copies data from the user form
‘ to the next blank row in the worksheet.