ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to use, create and configure ActiveX Controls List Boxes in Excel 2007 and Excel 2010

Updated on February 10, 2013

Introduction

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:

http://robbiecwilson.hubpages.com/hub/How-to-Use-Create-and-Configure-Form-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

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:

Example of an ActiveX Controls List Box created using Excel 2007 and Excel 2010.
Example of an ActiveX Controls List Box created using Excel 2007 and Excel 2010. | Source

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

Adding the Developer tab in Excel 2007.
Adding the Developer tab in Excel 2007. | Source

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)

Adding the Developer tab in Excel 2010.
Adding the Developer tab in Excel 2010. | Source

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

Configurable options available for ActiveX Controls List Boxes in Excel 2007 and Excel 2010.
Configurable options available for ActiveX Controls List Boxes in Excel 2007 and Excel 2010. | Source

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

Microsoft Visual Basic configuration screen in Excel 2007 and Excel 2010.
Microsoft Visual Basic configuration screen in Excel 2007 and Excel 2010. | Source

Remove all the text and paste in the following

Private Sub ListBox1_LostFocus()

Dim listItems As String, i As Long

With ListBox1

For i = 0 To .ListCount - 1

If .Selected(i) Then listItems = listItems & .List(i) & ", "

Next i

End With

If Len(listItems) > 0 Then

Range("AB26") = Left(listItems, Len(listItems) - 2)

Else

Range("AB26") = ""

End If

End Sub

Visual Basic code added to a List Box to list items selected in Excel 2007 and Excel 2010 separated by a ", ".
Visual Basic code added to a List Box to list items selected in Excel 2007 and Excel 2010 separated by a ", ". | Source

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.

Conclusion

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:

Examples of Form Controls and ActiveX Controls Option (or Radio) Buttons created in Excel 2007 and Excel 2010.
Examples of Form Controls and ActiveX Controls Option (or Radio) Buttons created in Excel 2007 and Excel 2010. | Source

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:

http://robbiecwilson.hubpages.com/hub/Using-Form-Controls-and-ActiveX-Controls-Option-Buttons-or-Radio-Button-in-Excel-2007-and-Excel-2010

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:

Scroll Bars:

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

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 created in Excel 2007 and Excel 2010. The top bar is a Form Control bar, while the bottom one is an ActiveX Controls Scroll Bar.
Scroll Bars created in Excel 2007 and Excel 2010. The top bar is a Form Control bar, while the bottom one is an ActiveX Controls Scroll Bar. | Source
Example of a Spin Button created using Excel 2007 and Excel 2010.
Example of a Spin Button created using Excel 2007 and Excel 2010. | Source

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.

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

Example of a Command Button with its Visual Basic code alongside, created using Excel 2007 and Excel 2010.
Example of a Command Button with its Visual Basic code alongside, created using Excel 2007 and Excel 2010. | Source

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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi BetoRoman,

      Thanks for your very kind comment, I am happy that you found my guide helpful and useful.

    • profile image

      BetoRoman 3 years ago

      Excellent. ...very helpful. ... Well explained. ....

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Many thanks for your comment.

      Do you mean have a List Box that has rows rather than columns? I don't think that this is possible. Of course, you could create a series of single row ActiveX List boxes and group them and this would create the same effect.

    • profile image

      Pete Leung 4 years ago

      Good guide but can selection output in rows instead of column ?

    • profile image
      Author

      Robbie C Wilson 4 years ago

      Hi! Thanks for your kind comment, I am glad you found my guide useful. I have not come across such an error before, which version of Excel are you using?

    • Ikhsan Ramlan profile image

      Ikhsan Ramlan 4 years ago

      Thank you for perfectly helpful guide, especially for the person with very superficial knowledge on Advance Excel application.

      Q

      How do I fixed the missing character in LinkedCell after user select the option in ListBox?

      Example 1 : User select 20,21,22

      Result in LinkedCell : 20,21,2

      Example 2: User only select 1 option (20)

      Result in LinkedCell : 2

    Click to Rate This Article