Creating a User Form

Figure 13-2 shows the UserForm in the VBE window.

Excel Vba Userform Projekte
Figure 13-2

The name of the UserForm was changed from the default name UserForml to frmPersonal. You do this in the first entry, (Name), in the Properties window. The Caption property is changed to Personal Data. The controls were added from the Toolbox.

There are two TextBox controls at the top of the form for name and age data. There are two option buttons (also known as radio buttons) for Male and Female, which are inside a frame control.

When you want to have a frame around other controls, you must insert the frame first, and then insert the controls into the frame.

There is also a CheckBox for Married, a ListBox for Department, and a CommandButton for OK.

It is a good idea to give your UserForms and controls descriptive names that identify what type of object they are and what their purpose is. The lowercase three-character prefix identifies the object type. For example, you use frm for a UserForm, scb for a scrollbar, and txt for a TextBox. The capitalized words that follow identify the control's purpose. This makes it much easier to write and maintain the VBA code that manipulates these objects.

The name of the first TextBox was changed to txtName, and the ControlSource property of txtName was entered as Sheet1!B2. The name of the second TextBox was changed to txtAge, and the ControlSource property of txtAge was entered as Sheet1!B3. Similar changes were made to the other main controls. The changes are summarized in the following table.

Control

Name

ControlSource

TextBox

txtName

Sheet1!B2

TextBox

txtAge

Sheet1!B3

OptionButton

optMale

Sheet1!C4

OptionButton

optFemale

Sheet1!D4

CheckBox

chkMarried

Sheet1!B5

ListBox

1stDepartment

Sheet1!B6

CommandButton

cmdOK

When you assign a ControlSource property to a worksheet cell, the cell and the control are linked in both directions. Any change to the control affects the cell, and any change to the cell affects the control.

The descriptive titles on the form to the left of the TextBoxes and above the ListBox show that the departments are Label controls. The Caption properties of the Label controls were changed to Name, Age, and Department. The Caption property of the frame around the OptionButton controls was changed to Sex, and the Caption properties of the option buttons were changed to Male and Female. The Caption property of the CheckBox was changed to Married.

The Male and Female option buttons can't be linked to B4. It is not appropriate to display the values of these controls directly, so the following IF function in cell B4 converts the True or False value in cell C4 to the required Male or Female result:

=IF(C4=TRUE,"Male","Female")

Although you only need to set cell C4 to get the required result, you need to link both option buttons to separate cells if you want the buttons to display properly when the UserForm is shown.

The RowSource property of lstDepartment was entered as Sheet1!A11:A18. It is good practice to create names for the linked cells and use those names in the ControlSource, rather than the cell references used here, but this extra step has been omitted to simplify this example.

The following Click event procedure was created for the button in the code module behind the UserForm:

Private Sub cmdOK_Click()

Unload Me End Sub

Me is a shortcut keyword that refers to the UserForm object containing the code. Me can be used in any class module to refer to the object the class module represents. If you want to access the control values later in your VBA code, you must use the Hide method, which leaves the UserForm in memory. Otherwise, the Unload statement removes the UserForm from memory and the control values are lost. You will see examples that use Hide shortly.

Clicking the x in the top-right corner of the UserForm will also dismiss the UserForm. This unloads the UserForm so that it is removed from memory. You will see how to prevent this later.

0 0

Post a comment