Microsoft Office Tutorials and References
In Depth Information
Creating a UserForm: An Example
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
‘ 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
Activate Excel and click the CommandButton again to display the UserForm and then
re-un the procedure again.
You’ll find that the UserForm controls now function correctly. You can use them to add
new names to the list in the worksheet.
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.
Validating the data
Play around with this example some more, and you’ll find that it has a small problem: It doesn’t
ensure that the user actually enters a name into the text box. To make sure that the user enters a
name, insert the following code in the OKButton_Click procedure, before the text is
transferred to the worksheet. It ensures that the user enters a name (well, at least some text) in the
TextBox. If the TextBox is empty, a message appears, and the focus is set to the TextBox so that
the user can try again. The Exit Sub statement ends the procedure with no further action.