Create User Forms in Microsoft Excel-P1 | Connecting IT

Create User Forms in Microsoft Excel-P1

Saturday, 2 October 2010 by nkoknki | 0 comments

The Course Booking Form

The Course Booking Form is a simple form illustrating the principles of UserForm design and the associated VBA coding.

It uses a selection of controls including text boxes, combo boxes, option buttons grouped in a frame, check boxes and command buttons.

When the user clicks the OK button their input is entered into the next available row on the worksheet.

Description of the Form:

There are two simple text boxes (Name: and Phone:) into which the user can type free text, and two combo boxes (Department and Course) that let the user to pick an item from the list.

There are three option buttons (Introduction, Intermediate and Advanced) grouped in a frame (Level) so that the user can choose only one of the options.

There are two check boxes (Lunch Required and Vegetarian) that, because they are not grouped in a frame, can both be chosen if required. However, if the person making the booking does not want lunch we do not need to know whether or not they are vegetarian. So, the Vegetarian check box is greyed-out until required.

There are three command buttons (OK, Cancel and Clear Form) each of which performs a pre-defined function when clicked.

The Control Properties Settings:

*Control*	*Type*	Property	*Setting*
UserForm	UserForm	Name	frmCourseBooking
		Caption	Course Booking Form
Name	Text Box	Name	txtName
Phone	Text Box	Name	txtPhone
Department	Combo Box	Name	cboDepartment
Course	Combo Box	Name	cboCourse
Level	Frame	Name	fraLevel
		Caption	Level
Introduction	Option Button	Name	optIntroduction
Intermediate	Option Button	Name	optIntermediate
Advanced	Option Button	Name	optAdvanced
Lunch Required	Check Box	Name	chkLunch
Vegetarian	Check Box	Name	chkVegetarian
		Enabled	False
OK	Command Button	Name	cmdOk
		Caption	OK
		Default	True
Cancel	Command Button	Name	cmdCancel
		Caption	Cancel
		Cancel	True
Clear Form	Command Button	Name	cmdClearForm

Building the Form

If you want to build the form yourself, simply copy the layout shown in the illustration above. Follow the steps below:

1. Open the workbook that you want the form to belong in (UserForms like macros have to be attached to a workbook) and switch to the Visual Basic Editor.

2. In the Visual Basic Editor click the Insert UserForm button (or go to Insert > UserForm).

3. If the toolbox does not appear by itself (first click the form to make sure it isn’t hiding) click the Toolbox button (or go to View > Toolbox).

4. To place a control on the form click the appropriate button on the toolbox then click the form. Controls can be moved by dragging them by their edges, or resized by dragging the buttons around their perimeter.

5. To edit the properties of a control, make sure the chosen control is selected then make the appropriate changes in the Properties window. If you can’t see the properties window go to View > Properties Window.

6. To remove a control from the form, select it and click the Delete key on your keyboard.

A UserForm will not actually do anything until the code that drives the form and its various controls is created. The next step is to write the code that drives the form itself.

Adding the Code: 1 Initialising the Form

Initialising the Form:

Most forms need some kind of setting up when they open. This may be setting default values, making sure field are empty, or building the lists of combo boxes. This process is called Initialising the Form and it is taken care of by a macro called UserForm_Initialize (in case you are confused by my varying spelling of the word "initialis(z)e", it's because I speak English and VBA speaks American - but don't worry, VBA will spell it for you!). Here's how to build the code to initialise the Course Booking Form:

1. To view the form’s code window go to View > Code or click F7.

2. When the code window first opens it contains an empty UserForm_Click() procedure. Use the drop-down lists at the top of the code window to choose UserForm and Initialize. This will create the procedure you need. You can now delete the UserForm_Click() procedure.

3. Enter the following code into the procedure:

Private Sub UserForm_Initialize()
    txtName.Value = ""
    txtPhone.Value = ""
    With cboDepartment
        .AddItem "Sales"
        .AddItem "Marketing"
        .AddItem "Administration"
        .AddItem "Design"
        .AddItem "Advertising"
        .AddItem "Dispatch"
        .AddItem "Transportation"
    End With
    cboDepartment.Value = ""
    With cboCourse
        .AddItem "Access"
        .AddItem "Excel"
        .AddItem "PowerPoint"
        .AddItem "Word"
        .AddItem "FrontPage"
    End With
    cboCourse.Value = ""
    optIntroduction = True
    chkLunch = False
    chkVegetarian = False
    txtName.SetFocus

End Sub

How the Initialise Code Works:

The purpose of the UserForm_Initialize() procedure is to prepare the form for use, setting the default values for the various controls and creating the lists that the combo boxes will show.

These lines set the contents of the two text boxes to empty:

txtName.Value = ""
txtPhone.Value = ""

Next come the instructions for the combo boxes. First of all the contents of the list are specified, then the initial value of the combo box is set to empty.

With cboDepartment
    .AddItem "Sales"
    .AddItem "Marketing"
    (as many as necessary…)
End With
cboDepartment.Value = ""

If required an initial choice can be made from the option group, in this case:

optIntroduction = True

Both check boxes are set to False (i.e. no tick). Set to True if you want the check box to appear already ticked:

chkLunch = False
chkVegetarian = False

Finally, The focus is taken to the first text box. This places the users cursor in the text box so that they do not need to click the box before they start to type:

txtName.SetFocus

Adding the Code: 2 Making the Buttons Work
Adding the Code 3: Manipulating the Form (Please wait)

Source: Exceltips

Excel tips

Connecting IT

Create User Forms in Microsoft Excel-P1

The Course Booking Form

Property

Building the Form

Adding the Code: 1 Initialising the Form

Initialising the Form:

0 comments:

Post a Comment

Learning English

Followers

Visitors