How to Use, Create and Configure Form Controls List Boxes in Excel 2007 and Excel 2010

Hi and welcome to my latest in the series on Form and ActiveX Controls in Excel 2007 and Excel 2010. Today, we will investigate the List Box. As the name suggests, the List Box allows you to create a box containing a list from which the user can select either one or any number of items.

List Boxes are very similar to Combo boxes, however:

• Combo boxes display the items in drop down list, whereas
• List Boxes display all the items at once provided there is sufficient space to display them
• Combo boxes are preferable when real estate on the worksheet is an issue
• If you want to display the entire list for the user to see a List box would be more appropriate
• Also, if you want the user to be able to select a number of items from the list provided, you should use an ActiveX List Box

The figure below shows the difference between ActiveX and Form Controls List Boxes and also Combo Boxes. From left to right the figure shows:

• Form Controls List Box
• ActiveX Controls List Box where a user can select only one item
• ActiveX Controls List Box where a user can select any number of items
• A Combo Box

I have a hub that goes into creating and configuring both Form Controls and ActiveX Controls Combo Boxes in Excel 2007 and Excel 2010 in far greater detail. This hub can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

As with the majority of Excel’s Controls, they are available in two types; Form Controls and ActiveX Controls. Form Controls are much simpler to create, ActiveX Controls are more advanced are more configurable and can also have Visual Basic code assigned to it.

Note: If you need to create a list where a user can select more than one item from the list, you should use an ActiveX List Box.

In part one of this two part hub, I will look at using Form Controls to create simple List Boxes, in part two I will look at creating more complex List Boxes using ActiveX Controls. That hub can be found here:

http://robbiecwilson.hubpages.com/hub/Using-creating-and-configuring-ActiveX-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

In this hub, we will be creating a panel where users of our workbook can select the music they wish to buy. The final result will look similar to mine below.

To begin, we need to ensure that the Developer tab is present in Excel. To add this tab in Excel 2007 should it be absent:

• Click the Excel button
• Select Excel Options
• While on the Popular tab, select Show Develop tab in the Ribbon

For Excel 2010, the process of adding the Developer tab is a little different:

• Select the File menu
• Then click Options
• On the Customise Ribbon tab, select Developer as shown below

Before creating the List Box, we need to create the items that the List Box will list. Simply type them into a column one item per cell.

Note: You may wish to hide the column once you have created the List Box to ensure that they are not changed or the cells deleted.

Note: Unless advised otherwise, the process for creating List Boxes in Excel 2010 is identical to Excel 200.

Now we create the List Box itself:

• Click on the Insert button which is in the Controls group on the Developer tab
• Select List Box from the Forms Controls section
• The cursor will change to a +
• Draw the outline of your List Box
• This will create a blank List Box as shown below

To configure the List Box, right click on it and select Format Control

• On the Size tab, you can precisely resize your List Box
• On the Control tab, the Input Range is the list of items you want displayed in your List Box
• Cell Link is the cell you want the item that the user selected to be displayed in
• Selection Type has to be Single (if you want Multi or Extend, you should create an ActiveX Controls List Box)
• 3-D Shading adds 3-D shading to your List Box
• Click OK and your List Box will be configured

When we select an item in the List Box Excel returns a number (e.g. if I select Billy Joel, Excel puts 5 in R3). This is because Billy Joel is the fifth entry in the list. We need to convert this into a more useful result so that when a user clicks Billy Joel, Excel 2007 (or Excel 2010) puts “Billy Joel” into a cell. To do this, we will use an IF command

=IF(R3=5,"Billy Joel")

As we have more than one in the list, we need to have an IF statement for each of the artists available as shown below for the first three items.

=IF(R3=1,"Pink Floyd",IF(R3=2,"Bruce Springsteen",IF(R3=3,"R.E.M.")))

Now we create List Boxes and IF statements for the music format and quantity until we have the following:

The formula to convert our media List Boxes output to more useful text is:

=IF(R4=1,"CD",IF(R4=2,"LP",IF(R4=3,"DVD",IF(R4=4,"Cassette"))))

The Quantity List box does not need an IF statement as the List Box returns the same number as the quantity selected.

Now we have to tie it all together to create our final panel that will display all our list boxes and the output of the user’s selection.

Our List Boxes utilise the data in the box in the figure below for our lists (O5:O23) and their output is placed into R3:R5. The IF statements that convert these numbers to text are held in U12 and U13

Now we need to create the text that will go at the bottom of the panel of List Boxes to tell the user in text what they have selected.

Firstly, we use another IF statement.

=IF(R5=1,"One copy of",IF(R5=2,"Two copies of"))

This converts 1 to One copy of and 2 to Two copies of etc.

Finally, we need to join our two text cells together. To achieve this we use the CONCATENATE function.

=CONCATENATE(U13, " on ",U12)

This tells Excel to join U13 to U12 and to add “ on “ in the middle.

I have a hub that investigates the CONCATENATE function in more detail which can be found here:

http://robbiecwilson.hubpages.com/hub/Using-the-CONCATENATE-LEFT-RIGHT-and-MID-functions-in-Excel-2007

The final result of our list boxes and the formulas we have used can be seen below.

Today, we have looked at using Form Controls to create List Boxes which allow users to select items from a list. We have then used a variety of functions (IF and CONCATENATE) to convert this numerical data into data that reflects what the user selected. In my example, we created a simple panel that allows users to select music they want to purchase and then displays that selection below the List Boxes.

In addition to Combo Boxes and List Boxes, I have created hubs for a number of the other Controls that Excel 2007 and Excel 2010 have available.

Using a Command Button you can create a button that performs a specific function when pressed. The function is created using Visual Basic. In my hub, I create a button that resets all my Check Boxes or Combo Boxes are reset to default. My hub on Command Buttons can be found here:

http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-in-Excel-2007

Check boxes allow users to select or tick items in lists such as shopping lists or to-do lists. When Conditional Formatting is added to the list, not only are the Check Boxes ticked but the text can be changed to reflect whether it is completed or not. My hubs for Check Boxes and Conditional Formatting can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007

http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-in-Excel-2007

Finally, Scroll Bars and Spin Buttons allow users to quickly select numbers from a list (for example selecting their birthday or the interest rate and term of their chosen mortgage). Both Scroll Bars and Spin Buttons are very similar to buttons you see on websites and are very user friendly and easy to use.

Spin Buttons:

http://robbiecwilson.hubpages.com/hub/Creating-configuring-and-using-Form-Controls-and-ActiveX-Controls-Spin-Buttons-in-Excel-2007-and-Excel-2010

Scroll Bars:

http://robbiecwilson.hubpages.com/hub/Creating_and_configuring_Form_Controls_and_ActiveX_Controls_Scroll_Bars_in_Excel_2007_and_Excel_2010

Many thanks for reading, I do hope that you enjoyed this hub and found it informative and useful. Please join me in part two when we look at ActiveX Controls List Boxes. Please feel free to leave any comments you may have below.