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 want 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 practice file contains a list of cars that your friend is interested in and their prices. The list starts in cell K2. You can create a list box that displays this list of cars.  -
Select cell K2, and press Ctrl+Shift+* to select the entire block of cells. -
From the Insert menu's Name submenu, click Define. 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.  Combo Box  -
In the Control Toolbox, click Combo Box, press and hold the Alt key, and drag a rectangle from the upper left corner to the lower right corner of cell E2.  A combo box can have either of two styles. It can be a drop-down 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, you want the combo box to be a drop-down list box. -
In the Properties window, for the value of the Style property, select 2-fmStyleDropDownList.  -
Type C2 as the value of the LinkedCell property, and press the Enter key. The price from cell C2 appears as the value of the combo box. But because the combo box has a sunken appearance, the text in the box is too large to read. You can reduce the size of the font to make it visible. -
Click the ellipsis button at the right of the Font property, change the font size to 8 points, and click OK. Now you can change the combo box to retrieve the list of cars.  -
Type CarList as the value of the ListFillRange property, and press the Enter key. Nothing seems to happen, but the combo box now knows to get its list of values from the CarList range. You can also watch the value of cell C2 change when you select a new car from the combo box. Exit Design Mode  -
Click the Exit Design Mode button, click the arrow on the combo box, and select 90 Toyota Camry from the list. The name for the Toyota appears in the drop-down control, but also unfortunately in cell C2. The formula doesn't work well with a car name entered as the price.  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 used to populate the list contains an extra column with the car prices, you can tell the combo box to get the value from that second column. Retrieve the Price from the List Design Mode  -
Click the Design Mode button, and click the combo box. -
In the Properties window, type 2 as the value of the ColumnCount property. The ColumnCount property informs the combo box that there are really two columns of values in the ListFillRange. -
Type 2 as the value of the BoundColumn property, and press the Enter key. 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.  -
Turn off design mode, and click the arrow on the combo box.  -
Select 87 BMW 325 from the drop-down list of cars. The price changes to $4,959.  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 was dropped down, there was a horizontal scroll bar across the bottom. Even though there's plenty of room for the price, the combo box makes the price column just as wide as the car name column. As the 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. -
Turn on design mode, and select the combo box. -
In the Properties window, type 1 in; .5 in as the value of the ColumnWidths property (to specify 1 inch for the first column and 0.5 inch for the second), and press the Enter key. The displayed value of the property changes to 72 pt; 36 pt. This is the equivalent value in points. A point is equal to 1/72 inch. You can type the value of the property using inches (in), centimeters (cm), or points (pt), but the value is always displayed in points. -
Turn off design mode, and click the combo box arrow. The combo box, complete with multiple columns, looks great!  |
