How to use, create and configure ActiveX Controls List Boxes in Excel 2007 and Excel 2010
Hi and welcome to part two of my two part series on using List Boxes in Excel 2007 and Excel 2010. Using List boxes you can provide your user with a box that contains a list with items a user can select. You can then store which items the user selected for use later or display them directly in the workbook.
In part one, I looked at creating Form Controls List Boxes. These are excellent when you only want the user to select one item from your List Box such as selecting your birthday. My hub on creating and configuring Form Control List Boxes can be found here:
In this hub, we will be creating ActiveX Controls List Boxes. When you want users to be able to select more than one item in a List Box or if you want to add Visual Basic code to the List Box you will need to use an ActiveX List Box.
The example we will use in today’s hub is asking users of our spreadsheet to select their favourite dwarf (or dwarfs) from the Seven Dwarfs (mine is Dopey). We will end up with a List Box that looks like the one below:
Creating an ActiveX Controls List Box using Excel 2007 and Excel 2010
First ensure that you have the Developer tab. In Excel 2007, to enable it should it be missing:
- 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 different:
- Click on the File menu
- Select Options
- Choose the Customise Ribbon tab
- Select Developer in the Main Tabs section (illustrated below with the red arrow)
Configuring an ActiveX Controls List Box using Excel 2007 and Excel 2010
Now that we have created our List Box, the next stage is to configure it.
Note: Configuring List Boxes in Excel 2007 and 2010 is identical from here on in.
- On the Developer tab, in the Controls group select the Insert button and then List Box from the ActiveX Controls section.
- The cursor will change to a +
- Draw the outline of your List Box
- Create the list that will populate your List Box in a column, one item in each cell
- Select the Design Mode button
- Right click your List Box and select Properties (or select the Properties button from the Controls group)
This will open the Properties dialogue box
The first options to configure are:
- LinkedCell – this is where the items the user selected will appear in your workbook
- ListFillRange – this is where your list that Excel 2007 or 2010 uses to populate your List Box sits (Note: you can’t select the cells, you need to type them in)
- MultiSelect – allows you to select Single (user is able to select only one item), Multi (user can select as many items as they like) and Extended (user can use Shift and Control to select multiple items)
There are a number of other options you can choose to change the appearance of your List Box:
- BackColor – changes the background colour of your List Box
- BorderColor – allows you to choose another colour for the border
- ForeColour – use this option to change the text colour
- ListStyle – turn off (or on) the check boxes with this option
- Shadow – give your List Box a shadow with this option
- SpecialEffect – allows you to choose between a number of visual effects for your List Box including Raised, Sunken, Bump, Etched and Flat.
- Font – change the font that your List Box uses with this option
Now that you have created your List Box and formatted it in whichever wild and wonderful way you desired we need to add some Visual Basic code to the List Box.
Adding Visual Basic code to a List Box in Excel 2007 and Excel 2010
The next step is to add Visual Basic code to your List Box. Without additional code, Excel 2007 and 2010 will not correctly display the items that the user selects.
To add Visual Basic, first check you are in Design mode (the Design Mode button will be orange when Design Mode is on and blue if it is off, assuming you have not changed the colour scheme)
- Select your List Box and either right click and select View Code, or press the View Code button in the Controls group with the List Box selected
- You will see a screen similar to that below
Remove all the text and paste in the following
Private Sub ListBox1_LostFocus()
Dim listItems As String, i As Long
For i = 0 To .ListCount - 1
If .Selected(i) Then listItems = listItems & .List(i) & ", "
If Len(listItems) > 0 Then
Range("AB26") = Left(listItems, Len(listItems) - 2)
Range("AB26") = ""
Now to explain what it does!
The script uses the following Visual Basic commands to create a list of all the items selected separated with a" ," and then copies that to the cell specified (AB26 in my case).
- LostFocus() means that the List Box is no longer selected (the user has clicked elsewhere on the worksheet)
- .ListCount is the number of rows in your List Box
- .Selected is the item(s) selected by the user
- .List is the row and column number of the list item
- LEN is used to calculate the length of the text
- LEFT allows you to take data from a string beginning at the first character on the left
The only parts you need to change to customise it for your own use are (I have bolded the specific part you may change if you wish):
- If you want to use a different separator rather than a, then on the fifth line put a different separator in the “ “
If .Selected(i) Then listItems = listItems & .List(i) & ", " becomes
If .Selected(i) Then listItems = listItems & .List(i) & " and "
- You will also need to change line nine
Range("AB26") = Left(listItems, Len(listItems) - 2) becomes
Range("AB26") = Left(listItems, Len(listItems) - 5)
This is because the original command allows for two characters “, “ and it is now five “ and “ (make sure to allow for spaces) so that Excel can determine which part of the data is names of the dwarfs and which parts are the separator.
Finally, you will need to change the cell your list of dwarfs will appear.
- In line nine and eleven change the cell reference to the appropriate cell in your worksheet
Range("AB26") = Left(listItems, Len(listItems) - 2) becomes
Range(“B2”) = Left(listItems, Len(listItems) - 2)
Now that the VB code is completed to suit your needs, simply close the Microsoft Visual Basic window and turn off Design Mode by clicking the Design Mode button.
Your List Box is now configured and ready to use!
Note: the list of dwarfs selected will not update until the user clicks elsewhere on the worksheet.
Using List Boxes you can create boxes containing lists of items that users can select with their responses being recorded for future reference. Excel 2007 and 2010 allow you to create boxes that allow either single or multiple items to be selected and also allow users to use shift and control to select multiple items with ease (extended List Boxes).
In my example, I created a List Box that invited users to choose their favourite dwarfs from the Seven Dwarfs. This selection was then displayed using Visual Basic code in a cell underneath the List Box.
I have a number of hubs covering in detail other Controls available in Excel 2007 and Excel 2010. These include:
Option (or Radio) Buttons are ideal if you want users of your spreadsheet to select only one option from a list. ActiveX Controls allow you to change the colours and fonts of the buttons as well as enabling you to group them to allow multiple buttons to be clicked in one block. My hub on Option (or Radio) buttons can be found here:
Scroll Bars and Spin Buttons allow users to quickly and easily select numbers from a range. Scroll Bars are better for larger lists and Spin Buttons are ideal for selecting things such as the users date of birth. As both are widely used on the Internet, users find both controls easy to use. My hubs for both can be found here:
Command Buttons allow you to create a button in Excel that performs a specific function when pressed. In my hub below, I create a Command Button that when clicked resets all drop boxes on the worksheet to their default position using Visual Basic code.
Many thanks for reading and I hope that you found this hub interesting and informative. Please feel free to leave any comments you may have below.