February 2000

Create User-Friendly Forms with Combo Boxes

by Sean Kavanagh

Collecting data from employees can be a challenge. For instance, suppose you're in charge of purchasing supplies for your employees. Since budgets differ from department to department, you need to be sure your employees are aware of current prices when they make purchases so that they can submit accurate requests.

If you deal with specific vendors, it's easy for you to be aware of price changes, but communicating that to other employees is a different story. Let's say that employees purchase computers through you, configured for their personal work needs. Since PC part prices change rapidly, you may place an Excel file with all the current prices on a network drive that everyone has access to. However, if that's all you do, employees still have to fill out additional forms and calculate their costs. This can be cumbersome.

Fortunately, Excel provides a slick way to simplify this process, as shown in Figure A. You can use dropdown lists on a worksheet so that people can mix and match the parts they need to build the PCs that are ideal for their jobs. At the same time, Excel can calculate the cost for the PCs so employees know if they fit into their budgets. Then, users can email or fax the final configuration to you.

Figure A: Excel's dropdown list control lets you regulate the data used to calculate the total cost.
[ Figure A ]

In this article, we'll show you how to create dropdown lists on your forms. We'll also show you how to populate the lists and how to record which selections users make. Then, we'll show you how the selection results can be used to control the data used in formulas.

An overview of the combo box control

A combo box control usually behaves as a combination of a text box and a dropdown list. There are actually two types of combo boxes available to you in Excel 2000: an ActiveX control that you can interact with using VBA and a Microsoft Form control.

Although you'd probably think that all combo boxes would behave the same way, there are slight differences between the ActiveX and Form combo box controls. With an ActiveX combo box, you can select from a dropdown list of predefined choices or enter a value directly in the text box. The Form control combo box only lets you select choices from the dropdown list. The ActiveX control gives you greater flexibility, but we'll use the Form control since we only need the basic functionality it provides.

Getting started

For simplicity, we won't create the full example shown in Figure A. We'll create a simpler form with just a few initial combo boxes to show you one way to incorporate them into your forms. First, open a blank Excel workbook. If necessary, insert additional worksheets in the workbook so you have a total of four sheets.

Now, we'll set up the basics of the form. To rename the first worksheet, double-click on the Sheet1 tab of the workbook, type PC Worksheet, and press [Enter]. Save your worksheet using the same name. Then, re-create the basic layout shown in Figure B. You're now ready to add your Processor combo box control.

Figure B: We'll add combo box controls to this sample form.
[ FIgure B ]

Creating a combo box

To do this, you'll first need to display the Forms toolbar in Excel. Right-click on any visible Excel toolbar and select Forms to display the Forms toolbar, shown in Figure C. If you want, you can dock this toolbar by dragging it to the edge of the Excel workspace.

Figure C: The Forms toolbar contains an assortment of basic form controls.
[ Figure C ]

At this point, click the Combo Box button on the Forms toolbar. When your mouse pointer turns into a set of cross-hairs, draw a box that covers the cell next to the Processor heading, cell B11. If your combo box is taller than the height of the worksheet row, adjust the row height to allow the control to fit neatly within the row.

Your combo box is currently blank-let's add some items to it. This type of Form control looks to a worksheet range for the list of valid values. Although we could use a range on this sheet, we'll store all our lookup ranges on separate sheets to make the form neater. To create your source range, select Sheet2 and rename it Processor. Then, enter the sample data shown in Figure D. Note that cell D2 isn't actually blank-you need to enter at least one space in this cell, otherwise you'll get undesirable results later on. Format the prices to Currency, two decimal places.

Figure D: This data will be used to populate the Processor combo box.
[ Figure D ]

When you've finished entering the sample data, select range A1:D5. Then, choose Insert/Name/Define. If the name Processor isn't automatically displayed in the Names In Workbook text box, type it. Next, click Add and then click Close.

To populate your combo box with the data you've entered, click on the PC Worksheet tab and right-click on your combo box. Next, choose Format Control from the dropdown list and click on the Control tab, if it isn't already selected. In the Input Range text box, specify the source data by typing Processor!$B$2:$B$5.

You need to know that your combo box can only read data from one column. Also, make sure you don't accidentally include any column headings when you enter which range Excel should use as the source data.

There's one more aspect of your combo box you need to take care of before closing the Format Control dialog box. You've specified what information should be displayed in the combo box's dropdown list-now you need a way of recording what users select. You'll store the result in a worksheet cell. To do this, in the Cell Link text box, type Processor!$E$1. Finally, for a little extra flash, select the 3D Shading check box, as shown in Figure E, and click OK.

Figure E: Enter the range that contains the data for the dropdown list and the cell you want to store the selection result in.
[ Figure E ]

When the dialog box closes, you can click in any worksheet cell to deselect your combo box. Now try it out! After playing with the combo box for a bit, take a look at cell E1 on the Processor sheet. You'll notice that it contains a number. This number corresponds to which selection was made from the combo box-first, second, third, etc.

Now, create the combo boxes for Video and Sound using the previous steps, with the sample data shown in Figure F and Figure G. Rename the other worksheets Video and Sound, respectively. Also, make sure you remember to create named ranges for the data and that you need to enter spaces in cell D2 on each sheet. When you've finished, select None from all the combo boxes on the PC Worksheet sheet so that your workbook resembles the one shown in Figure H. Save your workbook and close the Forms toolbar at this point.

Figure F: Use this data for the Video combo box.
[ Figure F ]

Figure G: Use this data for the Sound combo box.
[ Figure G ]

Figure H: Your combo boxes are populated with the data from the supporting worksheets.
[ Figure H ]

Working with the combo box selection

Your combo box controls work well enough, but they don't really provide a lot of added value in their current states. You need to be able to take the value that a user selects from the combo box and display the relevant price and comment information.

You'll do this by using the VLOOKUP function. Since a combo box control returns a numeric value, we can use that value to look up the items in our data tables. First, in cell C11 of the PC Worksheet sheet, enter the formula 

=VLOOKUP(Processor!$E$1,Processor,3)
This formula takes the value in cell E1 on the Processor sheet (currently 1) and looks for it in the left-most column of the Processor named range. When it finds the value, it returns the value from the third column in the Processor range-in our case, 0. Note that the currency formatting isn't transferred to the PC Worksheet sheet.

At this point, finish setting up your form by entering the formulas shown in Table A. Then, select range C11:C13 and press [Ctrl]1 to display the Format Cells dialog box. On the Number sheet, select Currency from the Category list box and click OK. Finally, enter the formula =SUM(C11:C13) in cell C14 and save your file.

Go ahead and try out your form. As you can see in Figure I, you can create any combination of data and instantly see the impact on the total cost.

Table A: VLOOKUP formulas
Cell
Formula
D11 =VLOOKUP(Processor!$E$1,Processor,4)
C12 =VLOOKUP(Video!$E$1,Video,3)
D12 =VLOOKUP(Video!$E$1,Video,4)
C13 =VLOOKUP(Sound!$E$1,Sound,3)
D13 =VLOOKUP(Sound!$E$1,Sound,4)

Figure I: Combine different parts with the combo boxes and see the results instantly reflected in the Total.
[ FIgure I ]

Notes

Now that you've gone through the effort of setting up your combo boxes, you should protect the cells you don't want users to be able to alter before making your form available. To do this, select cell B3. Then, while holding down the [Ctrl] key, select cells B4, D3, F3, and F4. From the menu bar, select Format/Cells. Next, click on the Protection tab of the Format Cells dialog box, deselect the Locked check box, and click OK.

Next, select Tools/Protection/Protect Sheet to display the Protect Sheet dialog box and ensure that all the choices are checked. If you want, enter a password. Finally, click OK. Now you can enter data only in the appropriate cells. However, your combo box controls are still fully functional.

To prevent users from accessing the data that populates the combo boxes, you'll hide the source sheets. First, click on the Processor worksheet tab. Then, press [Shift] and select the Sound tab to select the three data sheets. Finally, select Format/Sheet/Hide from the menu bar.

Conclusion

Using controls can greatly enhance forms you create with Excel. In this article, we've shown you how to use the Form control combo box to provide users with dropdown lists of valid choices. We've also shown you one way in which you can use the result of their selections to control formula results.


Copyright © 2000, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.