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 imageAUTHOR

      Robbie C Wilson 

      4 years ago

      Hi BetoRoman,

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

    • profile image

      BetoRoman 

      4 years ago

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

    • profile imageAUTHOR

      Robbie C Wilson 

      5 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 

      5 years ago

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

    • profile imageAUTHOR

      Robbie C Wilson 

      5 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 

      5 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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)