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.

click to expand

  1. Select cell K2, and press Ctrl+Shift+* to select the entire block of cells.

  2. 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.

    click to expand

    Combo Box

  3. 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.

    click to expand

    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.

  4. In the Properties window, for the value of the Style property, select 2-fmStyleDropDownList.

    click to expand

  5. 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.

  6. 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.

    click to expand

  7. 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

  8. 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.

    click to expand

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

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

  2. 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.

  3. 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.

    click to expand

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

    click to expand

  5. Select 87 BMW 325 from the drop-down list of cars. The price changes to $4,959.

    click to expand

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.

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

  2. 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.

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

    The combo box, complete with multiple columns, looks great!

    click to expand

Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net