OFF: How to Add Data to a ComboBox or a ListBox
ID: Q161598
|
The information in this article applies to:
-
Microsoft Excel 97 for Windows
-
Microsoft Word 97 for Windows
SUMMARY
This article describes methods of populating a ComboBox or ListBox control.
MORE INFORMATIONMicrosoft 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 professionals 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 a Microsoft Certified Solution Provider
or the Microsoft fee-based consulting line at (800) 936-5200. For more information about
Microsoft Certified Solution Providers, please see the following page on the World Wide Web:
http://www.microsoft.com/mcsp/
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/overview/overview.asp
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: Linking a Combo Box or List Box to Cells on an Excel Worksheet
- 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
- Press ALT+F11 to start the Microsoft Excel Visual Basic Editor.
- On the Insert menu, click UserForm.
- Create a ComboBox control and a ListBox control on the user form.
- If the Properties window is not visible, click Properties Window on
the View menu.
- 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).
- 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.
- 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: Using a Visual Basic Macro to Populate a Combo Box or List Box
- Open a Microsoft Office Application such as Microsoft Excel or Word.
- Press ALT+F11 to start the Visual Basic Editor.
- On the Insert menu, click UserForm.
- Create a ComboBox and a ListBox control on the user form.
- 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.
- 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".
- 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.
Additional query words:
XL97 97 userform user form combo box list vba
Keywords : kbprg kbdta kbdtacode OffVBA KbVBA
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowto
|