Programming Combo and List Boxes

When working with combo and list boxes through VBA, you'll often want to start with just a simple unbound control (one that's not attached to any field in the form's underlying table or query) and then let VBA control the properties.

To add an unbound ComboBox or a ListBox control to a form, first make sure that the form is open in Design view and that the Toolbox (see Figure 10-2) is open. To prevent the Control Wizards from helping you create the control, click the wizard's button until it's no longer lit. Then follow these steps:

Figure 10-2:

Toolbox Control Wizards, List Box, and Combo Box buttons.

Figure 10-2:

Toolbox Control Wizards, List Box, and Combo Box buttons.

Control Wizards

List Box Combo Box

Control Wizards

List Box Combo Box

1. In the Toolbox, click either the Combo Box or List Box tool, depending on which you want to create.

If it's hard to tell one button from the other in the Toolbox, just point to any button in the Toolbox to see its name next to the mouse pointer.

2. In the form, click at about where you want the left edge of the control to appear.

3. If the wizard appears and you don't want to use it, click the Cancel button in the wizard.

After the combo box or list box is on your form, you can view its properties in the Properties sheet. As always, if the Properties sheet isn't already open, you can press F4 or right-click the control and choose Properties.

Like all controls, combo boxes and list boxes have lots of properties. The ones that you're most likely to refer to from VBA are summarized in the following list. The first name (bold) is the property name as it appears in the Properties sheet; the following name in parentheses is the name of the property as written in VBA.

i Name (.Name): Defines the name of the control.

i Row Source Type (.RowSourceType): Specifies where the list gets its data: from records in a Table/Query, from a simple Value List typed into the Row Source property, or a field list of field names from a table or query.

i Row Source (.RowSource): Depending on the Row Source Type, this can be a SQL statement that gets data from a table or query, a typed list of options, or the name of a table or query.

i Default Value (.DefaultValue): The item that's automatically selected when the form first opens.

i List Rows (.ListRows): (Combo box only) The number of items to shown in the drop-down menu.

ii List Width (.ListWidth): (Combo box only) The width of the dropdown menu. If Auto, the drop-down menu width is equal in width to the ComboBox control.

i Limit to List (.LimitToList): (Combo box only) If Yes, the user's entry in the combo box must match an item in its drop-down menu. Otherwise, whatever the user typed is rejected as an invalid entry.

i Value (.Value): The value currently contained within the control.

To name a control on a form, first click the control to select it. Then click the All tab in the Properties sheet. Set the Name property at the top of the All tab to whatever you want to name your control.

In addition to the properties from the Properties sheet, VBA has an Item Data(x) property (where x is a number) that lets you refer to each item in the list by its position in the list. The first item is always zero (0), so the first item in the list can be referred to as ItemData(0), the next item is ItemData(1), and then ItemData(2) on down to the end of the list.

A list box doesn't have a List Rows or List Width property because there is no drop-down list in a list box. The width and height of the ListBox control, as a whole, determine the width and length of the list. There is no Limit To List property for a list box because there is no optional text box in which the user could type a value. With a list box, the user is always required to choose an option in the list.

Combo boxes and list boxes are both examples of list controls (in that they show some sort of list to the user). After the preceding quick peek at some commonly used properties of those controls, read on to take a look at how you work those pups.

In forms Design, you can easily change a text box to a combo box to a list box or whatever. Just right-click the control that you want to change and then choose Change ToOxx (the type of control you want).

+1 0

Post a comment