Welcome to part two of my series on Excel. In part one I looked at how to set up checkboxes (http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007). These are very useful for recording situations where a yes / no answer or condition is required. In part two, I will examine Combo boxes. Combo boxes have a number of advantages over check boxes and other methods of gathering input from other users of your Excel spreadsheets.
- Combo boxes give you the option of a configurable list in a drop down menu as a posed to a check box which is binary (on / off, yes / no etc).
- You have more flexibility of response compared with check boxes and by linking the output of the combo box to another cell, the selected answer from the combo boxes drop down list can be stored as a number for further manipulation. You could for example, set up combo boxes so that people can select how often each day do a particular task and then take those responses and manipulate this data directly from the combo boxes.
- By defining the drop down lists you have the added advantage of controlling a person’s responses to a question to those options available which enables your data to be more robust and more clearly defined.
As with creating check boxes, the developer tab will have to be enabled to access them. Enabling them is also illustrated in part one (http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007).
Creating Combo boxes
The creation of a combo box in Excel 2007 is very straightforward. Click on the Developer Tab, then select Insert and click on Combo Box in the Forms Control section as shown below. As with a checkbox, Excel will allow you to specify the size and location of your combo box using the cursor. To resize and move the combo box, right click on the combo box you want to alter. You can move it when the cursor is the cross formed by two arrows or by selecting the circles you can resize it. Don’t copy the combo boxes just yet; there is one more step we have to do before we can duplicate our combo boxes.
Configuring Combo boxes
The main advantage of a combo box over a check box is the ability to create drop down lists. You can have anything appear in your list that your heart desires which gives you far more flexibility over checkboxes. To create the list, type the list into a series of cells in one column. I have used Yes, N/A and No in mine. To allow your combo box to use this list, right click on your combo box and select Format Control and then the Control Tab. This screen has two fields we will configure, firstly let’s create our drop down list.
Click on the small red arrow to the right of Input range and select your column of values. It is important that you do not erase these cells, so it may be a good idea to hide the column or the sheet they are on as changing these will change your drop down lists (select the column and then right click and select hide).
The second field we are configuring will be the cell link field. This field is used to create the data we will use for conditional formatting. If you do not intend to do so, you can ignore this field. If you wish to use conditional formatting, click the red arrow to the right of cell link as shown below and select a cell. Once completed, this column will be hidden as well. How this linking works is quite clever. Each item on your drop down list is given a number. So in my example, Yes = 1, N/A = 2 and No = 3. So I now have a cell that contains a numerical representation of my selection that I can manipulate for conditional formatting.
Creating more combo boxes
To create more combo boxes, right click on your first combo box and select copy and then paste. Combo boxes tend to snap into existing cells much more accurately which makes placing them with your Excel spreadsheets much simpler than check boxes which tend to end up in a bit of a mess requiring them to be moved around more.
One thing to note when copying and pasting combo boxes is that they will all be linked to the cell used in the first combo box. To tidy this up for conditional formatting later, right click each newly created combo box and select Format Control / Control and update the cell link to the correct cells.
Combo boxes in action
So now we have configured and created some combo boxes and you will have something along the lines of my spreadsheet below. The drop boxes in column C are linked to the cells in column A which now display the results of your selection from the combo box in a numerical format which can easily be manipulated.
Thanks for reading my hub on configuring combo boxes. Please join me for my hub on conditional formatting for combo boxes and check boxes in Excel 2007. I hope you have enjoyed reading my hub as much as I have enjoyed writing it and that you found it useful and informative. Please feel free to leave a comment below.
I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here