Excel VBA Control Tutorial

Microsoft Excel's abilities are greatly enhanced through the use of Visual Basic script, which lets you tailor Excel's default tools to your project's specific needs. Using custom user forms, with controls to receive user input, gives you full control over user-input Excel data. Entering the following program will show you how to use several Visual Basic controls.

Start the project by opening Excel and entering the Visual Basic IDE (integrated development environment). Enter the IDE with alt-F11.

Place the Controls

In the IDE's designer window (View>Object), drag and drop controls from the toolbox onto a user form as follows:

Place a Frame control onto the form. In the Properties window, change its caption to "Marital Status." With the frame control still selected, drag two option buttons inside the frame control. Drag two labels into the frame, each to the left of an option button. Give the labels the captions "Married," and "Single."

Drag a list box underneath the frame and its option buttons. Drag a label, which should automatically be named Label3, under the list box. Enter "Answer to security question," for its caption. To the right of Label3 drag a text box. Under the text box drag a button and give it the caption "Submit." Lastly, drag a label, which should be named Label4, to the right of the frame control. Make its caption blank.

The actions you've just taken created a typical form that collects data from a prospective new customer. You'll now use Visual Basic to transfer the customer's data from the controls onto an Excel worksheet.

Creating Code for the Form

Double click the form to enter the code window, and select these items from the two drop-down boxes near the top of the code window: UserForm, and Initialize. The IDE will create a blank Initialize sub. Inside that sub, enter this code, which populates the list box when the program starts:

ListBox1.List = Array("What's you favorite movie?", "In what city were you born?", "What is the sound of one hand clapping?")

As you did for the user form's Initialize subroutine, create blank subroutines for the option buttons' Click events, using the code window's drop-down boxes. In the Click events of both optionButton1 and 2, enter the following code: "marital." That's the name of a function you'll write now:

In a blank section of the code window, create the marital function:

Private Sub marital()
'Which button was selected?
If OptionButton1.Value = True Then
Label4.Caption = "married"
Else
Label4.Caption = "single"
End If

End Sub

Transfer the Form Data to the Worksheet

Create the blank subroutine for the command button's Click event using the code window's drop-down boxes, and enter this in that subroutine:

Range("a1") = Label4.Caption
Range("b1") = ListBox1.Value
Range("c1") = TextBox1.Value

This code transfers the form data to the worksheet.

Enter the last bit of code, a new subroutine that displays your form:

Public Sub showForm()
UserForm1.Show

End Sub

Return to Excel (Alt-F11) and run your macro (Developer>Macros, showForm). Enter data into the form, press its button, and watch the worksheet receive the form's values.

References

• Macro Examples Using Option Button Controls on a UserForm