Adding eventhandler procedures

In this section, I explain how to write the procedures that handle the events that occur when the dialog box is displayed.

1. Press Alt+F11 to activate the VBE.

2. Make sure the UserForm is displayed; double-click the Cancel button.

The VBE activates the Code window for the UserForm and provides an empty procedure named CancelButton_Click.

3. Modify the procedure as follows:

Private Sub CancelButton_Click() Unload UserForml

End Sub

This procedure, which is executed when the user clicks the Cancel button, simply unloads the dialog box.

4. Press Shift+F7 to redisplay UserForml.

5. Double-click the OK button and enter the following procedure:

Private Sub OKButton_Click() Dim NextRow As Long

' Make sure Sheetl is active Sheets("Sheet1").Activate

' Determine the next empty row

NextRow = Application.WorksheetFunction. _ CountA(Range("A:A")) + 1

' Transfer the name

Cells(NextRow, 1) = TextName.Text

' Transfer the sex

If OptionMale Then Cells(NextRow, 2) = "Male" If OptionFemale Then Cells(NextRow, 2) = "Female" If OptionUnknown Then Cells(NextRow, 2) = "Unknown"

' Clear the controls for the next entry TextName.Text = "" OptionUnknown = True TextName.SetFocus

End Sub

6. Now activate Excel and run the procedure again by clicking the Data Entry button.

The dialog box works just fine. Figure 18-3 shows how this looks in action.

Here's how it works:

1 First, the procedure makes sure that the proper worksheet (Sheet1) is active.

1 It then uses the Excel COUNTA function to count the number of entries in column A and to determine the next blank cell in the column.

1 Next, the procedure transfers the text from the TextBox to Column A.

i It then uses a series of If statements to determine which OptionButton was selected and writes the appropriate text (Male, Female, or Unknown) to column B.

i Finally, the dialog box is reset to make it ready for the next entry. Notice that clicking OK doesn't close the dialog box. To end data entry, click the Cancel button.

Figure 18-3:

Use the custom dialog box for data entry.

Figure 18-3:

Use the custom dialog box for data entry.

Validating the data

Play around with this routine some more and you find that the macro has a small problem: It doesn't ensure that the user actually enters a name into the TextBox. The following code — which is inserted in the OKButton_Click procedure before the text is transferred to the worksheet — ensures that the user enters some text in the TextBox. If the TextBox is empty, a message appears and the routine stops.

Make sure a name is entered If TextName.Text = "" Then

MsgBox "You must enter a name." Exit Sub End If

Now the dialog box works

After making these modifications, you find that the dialog box works flawlessly. In real life, you'd probably need to collect more information than just name and sex. However, the same basic principles apply. You just have to deal with more UserForm controls.

0 0

Post a comment