Adding event handler procedures

In this section, I explain how to write the procedures that will handle the events that occur when the UserForm is displayed. To continue the example, do the following:

1. Press Alt+F11 to activate the VBE.

2. Make sure the UserForm is displayed and double-click its Close button.

This will activate the Code window for the UserForm and insert an empty procedure named CloseButton_Click. Notice that this procedure consists of the object's name, an underscore character, and the event that it handles.

3. Modify the procedure as follows. (This is the event handler for the CloseButton's Click event.)

Private Sub CloseButton_Click() Unload UserForm1

End Sub

This procedure, which is executed when the user clicks the Close button, simply unloads the

UserForm.

4. Press Shift+F7 to redisplay UserForm1 (or click the View Object icon at the top of the Project Explorer window).

5. Double-click the OK button and enter the following procedure. (This is the event handler for the

OKButton's Click event.)

Private Sub OKButton_Click() Dim NextRow As Long

' Make sure Sheet1 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. Activate Excel and click the CommandButton again to display the UserForm. Run the procedure again.

You'll find that the UserForm controls now function correctly.

Here's how the OKButton_Click procedure works: First, the procedure makes sure that the proper worksheet (Sheet1) is active. It then uses Excel's COUNTA function to determine the next blank cell in column A. Next, it transfers the text from the TextBox control to column A. 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. 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 (and unload the UserForm), click the Close button.

0 0

Post a comment