Transferring Form Data to the Worksheet

When the user clicks the OK button, the form's selections should be written to the worksheet. The user can quit using the form at any time by clicking the Cancel button.

1. Double-click the OK button, and enter the cmdOK_Click procedure shown below:

Private Sub cmdOK_Click()

Me.Hide r = Application.CountA(Range("A:A")) Range("A1").Offset(r + 1, 0) = Me.lboxSystems.Value

If Me.optHard.Value = True Then

Range("A1").Offset(r + 1, 1) = "*" End If

If Me.optSoft.Value = True Then

Range("A1").Offset(r + 1, 2) = "*" End If

If Me.chkIBM.Value = True Then

Range("A1").Offset(r + 1, 3) = "*" End If

If Me.chkNote.Value = True Then

Range("A1").Offset(r + 1, 4) = "*" End If

If Me.chkMac.Value = True Then

Range("A1").Offset(r + 1, 5) = "*" End If

Range("A1").Offset(r + 1, 6) = Me.cboxWhereUsed.Value Range("A1").Offset(r + 1, 7) = Me.txtPercent.Value If Me.optMale.Value = True Then

Range("A1").Offset(r + 1, 8) = "*" End If

If Me.optFemale.Value = True Then

Range("A1").Offset(r + 1, 9) = "*"

End If Unload Me End Sub

The cmdOK_Click procedure begins by hiding the user form. The statement:

r = Application.CountA(Range("A:A"))

uses the Visual Basic CountA function to count the number of cells that contain data in column A. The result of the function is assigned to the variable r. The next statement:

Range("A1").Offset(r + 1, 0) = Me.lboxSystems.Value enters the selected list box item in a cell located one row below the last used cell in column A (r+1). Next, there are several conditional statements. The first one tells Visual Basic to place an asterisk in the appropriate cell in column B if the Hardware option button is selected. Column B is located one column to the right of column A, hence there's a "1" in the position of the second argument of the Offset method. The second If statement enters the asterisk in column C if the user selected the Software option button. Similar instructions record the actual check box values.

In column G, the procedure will enter the item selected in the Where Used combo box. Column H will show the value entered in the Percent (%) Used text box, and columns I and J will identify the gender of the person who submitted the survey.

0 0