XL97: How to Fill ListBox Control with Multiple Ranges
ID: Q161534
|
The information in this article applies to:
-
Microsoft Excel 97 for Windows
SUMMARY
This article contains an example that fills the list of a ListBox
control on a UserForm with data from multiple cell ranges.
MORE INFORMATION
You cannot use the RowSource property to bind a ListBox control to a
worksheet when you want the list to draw from multiple ranges. You must
loop through the various ranges with a Visual Basic for Applications
procedure and add the items to the list one at a time.
The following examples populate a ListBox control as it is loaded by using
the Initialize event for the UserForm.
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 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
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/overview/overview.asp
Populating the ListBox Directly from Worksheet Cells
To populate the ListBox, follow these steps:
- Close and save any open workbooks and then create a new workbook.
- On Sheet1, enter the following values:
A1: Planes C1: Alpha
A2: Trains C2: Bravo
A3: Automobiles C3: Charlie
- Start the Visual Basic Editor (press ALT+F11).
- On the Insert menu, click UserForm.
- Draw a ListBox control on the UserForm.
- Double-click the UserForm to open the Code window for the UserForm.
- In the module type the following code for the UserForm Initialize
event:
Private Sub UserForm_Intialize()
Dim Lrange As Range
Dim x As Variant
'Set the range to loop through
Set Lrange = Union(Sheet1.Range("A1:A3"), Sheet1.Range("C1:C3"))
'Loops through the ranges
For Each x In Lrange
'Adds an item to the list
Listbox1.AddItem x.Value
Next x
End Sub
- Run the UserForm.
The items in the ranges A1:A3 and C1:C3 on Sheet1 are added to the
list in ListBox1.
- Close the UserForm.
Using an Array to Populate the ListBox
It is also possible to assign the contents of a Visual Basic array as the
list of a ListBox control. The following example reads the values from the
worksheet into an array, and then assigns the array to the ListBox control
as the list:
- In the module, change the code for the UserForm Initialize event:
Private Sub UserForm_Initialize()
Dim Lrange As Range
Dim Larray() As Variant
Dim x As Variant
Dim ctr As Integer
'Set the range to loop through
Set Lrange = Union(Sheet1.Range("A1:A3"), Sheet1.Range("C1:C3"))
'Loops through the ranges
For Each x In Lrange
ReDim Preserve Larray(ctr)
'Add an item to the array
Larray(ctr) = x.Value
ctr = ctr + 1
Next x
'Assign the array to the listbox
ListBox1.List = LArray
End Sub
- Run the UserForm.
The items in the ranges A1:A3 and C1:C3 on Sheet1 are read into an
array and are then assigned to the list of ListBox1.
- Close the UserForm.
REFERENCES
For additional information about populating list boxes in earlier
versions of Microsoft Excel, please see the following article here
in the Microsoft Knowledge Base:
Q153603 XL: Macro to Fill a List Box with Multiple Ranges
For more information about ListBox Controls, click the Office Assistant,
type listbox, click Search, and then click to view "ListBox control"
Additional query words:
97 8.00 XL97
Keywords : kbprg kbdta kbdtacode KbVBA
Version : WINDOWS:
Platform : WINDOWS
Issue type :