How to add data to a ComboBox control or a ListBox control in Excel or Word

Article translations Article translations
Article ID: 161598 - View products that this article applies to.
This article was previously published under Q161598
For a Microsoft Excel for Mac version of this article, see 185388.
Expand all | Collapse all

On This Page

SUMMARY

This article describes methods of populating a ComboBox or ListBox control.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
You can use a control, such as a combo box or a list box, on a user form. In Microsoft Excel, you can use these controls in the worksheet. The methods for populating the controls are similar. The examples in this article use controls on a user form.

Method 1: How to Link a Combo Box or List Box to Cells on an Excel Worksheet

  1. Create a new workbook in Microsoft Excel. On Sheet1, type the following:
          A1: One     B1: Red
          A2: Two     B2: Green
          A3: Three   B3: Yellow
          A4: Four    B4: Blue
    					
  2. Press ALT+F11 to start the Microsoft Excel Visual Basic Editor.
  3. On the Insert menu, click UserForm.
  4. Create a ComboBox control and a ListBox control on the user form.
  5. If the Properties window is not visible, click Properties Window on the View menu.
  6. Click the ComboBox and scroll to RowSource in the Properties window.

    The window is divided into two sections. The left section contains the name of the property for the control. The right section contains the value of the property. Click in the right section of RowSource and type "sheet1!a1:a4" (without the quotation marks).
  7. Click the ListBox and scroll to RowSource in the Properties window. Click in the right section of RowSource and type "sheet1!b1:b4" (without the quotation marks).

    NOTE: If you omit the sheet reference, the ComboBox and ListBox controls are populated with the data from the cells from the worksheet that is active when you run the user form. To ensure that the data from the same worksheet is always used to populate the user form, include the sheet reference as shown in the examples. To populate the user form with data from the active sheet, do not include the sheet reference in the RowSource. When you do this, the macro uses data from the active sheet to populate the user form.
  8. Run the user form by clicking Run and then clicking Run Sub/UserForm.
One, Two, Three, and Four appear in the ComboBox control; and Red, Green, Yellow, and Blue appear in the ListBox control.

Methdod 2: How to Use a Visual Basic Macro to Populate a Combo Box or List Box

  1. Open a Microsoft Office Application such as Microsoft Excel or Word.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. On the Insert menu, click UserForm.
  4. Create a ComboBox and a ListBox control on the user form.
  5. Right-click the user form and click View Code. The Code window for the user form appears. Click Initialize in the Procedure list (on the right side).

    The first and last lines of the subroutine are automatically entered, and the insertion point is automatically placed between these two lines.
  6. Type the following code:
          ListBox1.List = Array("One", "Two", "Three", "Four")
          ComboBox1.List = Array("Red", "Green", "Yellow", "Blue")
    						
    This code should appear between the lines "Sub UserForm_Initialize()" and "End Sub".
  7. On the Run menu, click Run Sub/UserForm to run the user form.
One, Two, Three, and Four appear in the ListBox control; and Red, Green, Yellow, and Blue appear in the ComboBox control.

REFERENCES

For more information about user forms, click the Index tab in Microsoft Visual Basic Help, type the following text:
UserForm Object
and then double-click the selected text to go to the "UserForm Object" topic.

Properties

Article ID: 161598 - Last Review: August 19, 2010 - Revision: 7.0
APPLIES TO
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Word 2002
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Word 2000
  • Microsoft Excel 97 Standard Edition
  • Microsoft Word 97 Standard Edition
  • Microsoft Word 2010
  • Microsoft Office Word 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
  • Microsoft Office Excel 2007
  • Microsoft Office Word 2003
Keywords: 
kbcontrol kbdtacode kbhowto kbprogramming KB161598

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com