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 Cancel button. This will activate the Code window for the UserForm and insert an empty procedure named CancelButton_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 CancelButton's Click event.)

Private Sub CancelButton_Click()

Unload UserForm1 End Sub

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

4. Press Shift+F7 to redisplay UserForml (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() ' 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. 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. Figure 13-10 shows how this looks in action.

mm

1

A

B

C D

E

F

G

H

IT

1

Roger

Male

2

Jill

Female

CnmmsnrlFiiittnnl

3

Ram

Female

4

Jim Bob

Male

5

Frank

Male

6

7

Name:

Betty

Cancel

8

-

9

- Sex

I-

OK |

10

C Male

l_

11

12

P Eema

!*.!

13

r Unkm

Dwn

14

15

16

17

18

► in

H *

► N \Sheetl /

m

Figure 13-10: The information entered into the UserForm is added to the end of the data.

Figure 13-10: The information entered into the UserForm is added to the end of the data.

Here's how the OKButton_Click procedure works: First, the procedure makes sure that the proper worksheet (Sheetl) 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 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 Cancel button.

0 0

Post a comment