Creating a UserForm: An Example


If you've never created a UserForm, you might want to walk through the example in this section. The example includes step-by-step instructions for creating a simple dialog box and developing a VBA procedure to support the dialog box.

This example uses a UserForm to obtain two pieces of information: a person's name and sex. The dialog box uses a TextBox control to get the name and three OptionButtons to get the sex (Male, Female, or Unknown). The information collected in the dialog box is then sent to the next blank row in a worksheet.

Creating the UserForm

Figure 13-9 shows the finished UserForm for this example. For best results, start with a new workbook with only one worksheet in it. Then follow these steps:

  1. Press Alt+F11 to activate the VBE.

  2. In the Project window, select the workbook's project and choose Insert image from book UserForm to add an empty UserForm.

  3. The UserForm's Caption property will have its default value: UserForm1 . Use the Properties window to change the UserForm's Caption property to Get Name and Sex . (If the Properties window isn't visible, press F4.)

  4. Add a Label control and adjust the properties as follows :

    Open table as spreadsheet

    Property

    Value

    Accelerator

    N

    Caption

    Name:

    TabIndex

  5. Add a TextBox control and adjust the properties as follows:

    Open table as spreadsheet

    Property

    Value

    Name

    TextName

    TabIndex

    1

  6. Add a Frame control and adjust the properties as follows:

    Open table as spreadsheet

    Property

    Value

    Caption

    Sex

    TabIndex

    2

  7. Add an OptionButton control inside the Frame and adjust the properties as follows:

    Open table as spreadsheet

    Property

    Value

    Accelerator

    M

    Caption

    Male

    Name

    OptionMale

    TabIndex

  8. Add another OptionButton control inside the Frame and adjust the properties as follows:

    Open table as spreadsheet

    Property

    Value

    Accelerator

    F

    Caption

    Female

    Name

    OptionFemale

    TabIndex

    1

  9. Add yet another OptionButton control inside the Frame and adjust the properties as follows:

    Open table as spreadsheet

    Property

    Value

    Accelerator

    U

    Caption

    Unknown

    Name

    OptionUnknown

    TabIndex

    2

    Value

    True

  10. Add a CommandButton control outside the Frame and adjust the properties as follows:

    Open table as spreadsheet

    Property

    Value

    Caption

    OK

    Default

    True

    Name

    OKButton

    TabIndex

    3

  11. Add another CommandButton control and adjust the properties as follow s:

    Open table as spreadsheet

    Property

    Value

    Caption

    Close

    Cancel

    True

    Name

    CloseButton

    TabIndex

    4

image from book
Figure 13-9: This dialog box asks the user to enter a name and a sex.
Tip  

When you are creating several controls that are similar, you may find it easier to copy an existing control rather than create a new one. To copy a control, press Ctrl while you drag the control to make a new copy of it. Then adjust the properties on the copied control.

Writing code to display the dialog box

Next, you add an ActiveX CommandButton to the worksheet. This button will execute a procedure that displays the UserForm. Here's how:

  1. Activate Excel. (Alt+F11 is the shortcut key combination.)

  2. Choose Developer image from book Controls image from book Insert and click CommandButton from the ActiveX Controls section.

  3. Drag in the worksheet to create the button.

    If you like, you can change the caption for the worksheet CommandButton. To do so, right-click the button and choose CommandButton Object image from book Edit from the shortcut menu. You can then edit the text that appears on the CommandButton. To change other properties of the object, right-click and choose Properties. Then make the changes in the Properties box.

  4. Double-click the CommandButton.

    This activates the VBE. More specifically , the code module for the worksheet will be displayed, with an empty event handler procedure for the worksheet's CommandButton.

  5. Enter a single statement in the CommandButton1_Click procedure (see Figure 13-10). This short procedure uses the Show method of an object ( UserForm1 ) to display the UserForm.

image from book
Figure 13-10: The CommandButton1_Click procedure is executed when the button on the worksheet is clicked.

Testing the dialog box

The next step is to try out the procedure that displays the dialog box.

Note  

When you click the CommandButton on the worksheet, you'll find that nothing happens. Rather, the button is selected. That's because Excel is still in design mode - which happens automatically when you insert an ActiveX control. To exit design mode, click the Developer image from book Controls image from book Design Mode button. To make any changes to your CommandButton, you'll need to put Excel back into design mode.

When you exit design mode, clicking the button will display the UserForm (see Figure 13-11).

image from book
Figure 13-11: The CommandButton's Click event procedure displays the UserForm.

When the dialog box is displayed, enter some text into the text box and click OK. Nothing happens - which is understandable considering you haven't yet created any event handler procedures for the UserForm.

Note  

Click the Close button in the UserForm title bar to get rid of the dialog box.

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.

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 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.

 '   Make sure a name is entered     If TextName.Text = "" Then         MsgBox "You must enter a name."         TextName.SetFocus     Exit Sub End If 

The finished dialog box

After making all these modifications, you'll find that the dialog box works flawlessly. (Don't forget to test the hot keys.) In real life, you'd probably need to collect more information than just name and sex. However, the same basic principles apply. You just need to deal with more UserForm controls.

CD-ROM  

A workbook with this example is available on the companion CD-ROM in a file named image from book  get name and sex.xlsm .




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net