Retrieving a Value from a List

You could specify the price of the car by creating another scroll bar, but the price of a car is actually determined by which car you want to buy. You know that your friend has been looking through the classified ads and has come up with a list of used cars to consider. You can make the worksheet easy to use by allowing your friend to select the description of the car and have the price appear automatically in the Price cell.

Prepare a List of Cars

The Chapter10 workbook contains a list of the cars your friend is interested in, along with their prices. The list starts in cell K2. You can create a list box that displays this list of cars.

To make the formulas easier, you’ll start by naming the range that contains the list.

1. Select the range K2:L8. Then right-click the range, and click Name a Range on the shortcut menu. In the New Name dialog box, type CarList as the name of the list, and click OK.

   The defined name contains both the list of car names and the corresponding list of prices.

2. On the Developer tab of the Ribbon, click the Insert arrow, and click Combo Box (ActiveX Control). Then press and hold the Alt key, and drag a rectangle from the upper-left corner to the lower-right corner of cell E2.

   
   Combo Box (ActiveX Control)

   

   A combo box can have either of two styles. It can be a list box, allowing you to select only items from the list, or it can be a list box combined with an edit box, allowing you to enter new values as well as select from the list. Because you want to confine your friend to the existing list of cars and prices, you want the combo box to be a simple list box.

3. In the Properties window, for the value of the Style property, select 2 – fmStyleDropDownList.

   

   The list box has a sunken appearance. On a worksheet, it would look better if there simply appeared to be an arrow in the cell. You can get that effect by changing a couple of properties on the control.

4. In the Properties window, change the value of the SpecialEffect property to 0 – fmSpecialEffectFlat and the value of the BackStyle property to 0 – fmBackStyleTransparent. Click anywhere on the worksheet so that you can see the effect of your changes to the button's properties.

   

5. Select the ComboBox control. Change the value of the LinkedCell property to Price and press Enter.

   The price from cell C2 appears as the value of the combo box. Now you can change the combo box to retrieve the list of cars.

6. Change the value of the ListFillRange property to CarList and press Enter.

   Nothing seems to happen because the value in cell C2 doesn’t match any of the values from the list. But the combo box now knows to get its list of values from the CarList range. You can watch the value of cell C2 change when you select a new car from the combo box.

7. Click the Design Mode button to turn it off. Then click the arrow on the combo box, and select 90 Toyota Camry from the list.

   The name of the car appears in the combo box, but it also appears in cell C2, and the formulas don’t work well when a car name is entered as the price.

   

Retrieve the Price from the List

You can now select a car name from the combo box, but you want the combo box to put the price of the car-not the name of the car-into cell C2. Because the CarList range you are using to populate the list contains a column with the car prices, you can tell the combo box to get the value from that column.

1. Click the Design Mode button, and then click the combo box.

2. In the Properties window, change the ColumnCount property to 2 and press Enter.

   The ColumnCount property informs the combo box that there are two columns of values in the ListFillRange.

3. Change the BoundColumn property to 2 and press Enter.

   The BoundColumn property tells the combo box which column’s value to put into the linked cell. And sure enough, the price of the Toyota, $5,950, appears in the cell.

   

4. Turn off design mode, and click the arrow on the combo box.

   

5. Select 87 BMW 325 from the list of cars.

   The price changes to $4,959, and the other formulas recalculate as well.

Now your friend won’t accidentally calculate the payment for a $1,500,000 car. Your friend can just select various cars from the list, and Excel will automatically insert the correct price in the Price cell.

Set the Column Widths

The combo box works fine, but while the list is displayed, it has a horizontal scroll bar across the bottom, and car names are cut off. The combo box makes the price column as wide as the car name column. By default, a combo box uses the same width for each column. If, as in this example, you want the columns to have different widths, you can manually control the column widths.

1. Drag the heading for column E to the right to set the width to 30.

2. Turn on design mode, and select the combo box.

3. In the Properties window, change the value of ColumnWidths to 1.5 in; .5 in, and press Enter.

   This specifies 1.5 inches for the first column and 0.5 inch for the second column. The displayed value of the property changes to 108 pt; 36 pt, the equivalent value in points. (A point is equal to 1/72 inch.) You can type the value of the property in inches (in), centimeters (cm), or points (pt), but the Properties window always displays the value in points.

4. Turn off design mode, and click the combo box arrow.

   

   The combo box, with its new customized column widths, looks great!