XL97: How to Add Data to a ComboBox or a ListBox

• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel, there are two ways of populating a ComboBox or ListBox control. You can link the control to worksheet cells or you can populate the control with a Microsoft Visual Basic for Applications macro.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

Linking a Combo Box or List Box to Cells on a 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 and a ListBox control on the UserForm.

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.

1. 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 UserForm. To ensure that the data from the same worksheet is always used to populate the UserForm, include the sheet reference as shown in the examples. To populate the UserForm 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 UserForm.

2. Run the UserForm by clicking Run, and then clicking Run Sub/UserForm.

Using a Visual Basic Macro to Populate a Combo Box or List Box

1. Create a new workbook in Microsoft Excel.

2. Press ALT+F11 to start the Microsoft Excel Visual Basic Editor.

3. On the Insert menu, click UserForm.

4. Create a ComboBox and a ListBox control on the UserForm.

5. Right-click the UserForm and click View Code. The Code window for the UserForm 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:

         myarray1 = Array("One", "Two", "Three", "Four")
         myarray2 = Array("Red", "Green", "Yellow", "Blue")
         For x = 0 to 3
             ListBox1.AddItem myarray1(x)
         Next x
         For y = 0 to 3
             ComboBox1.AddItem myarray2(y)
         Next y
   
      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 UserForm.

8. 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 UserForms, click the Index tab in Microsoft Visual Basic Help, type the following text

   UserForm Object