Loading array into list box
 

I'm trying to load an array into a list box. When I execute the following
macro, it tells me that I have a subscript out of range for the AddItem code.

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Sheet1Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
UserForm1.Show
End Sub

Loading array into list box
 

Use this event in the userform module

Private Sub UserForm_Initialize()
Me.ListBox1.List = Sheet1.Range("A1:A4").Value
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"r wilcox" wrote in message ...
I'm trying to load an array into a list box. When I execute the following
macro, it tells me that I have a subscript out of range for the AddItem code.

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Sheet1Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
UserForm1.Show
End Sub

Loading array into list box
 

It's 2 dimensional

Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr, 1)
Next
UserForm1.Show


--

HTH

RP
(remove nothere from the email address if mailing direct)


"r wilcox" wrote in message
...
I'm trying to load an array into a list box. When I execute the following
macro, it tells me that I have a subscript out of range for the AddItem
code.

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Sheet1Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
UserForm1.Show
End Sub

Loading array into list box
 

Just use

UserForm1.ListBox1.List = MyArray

no need to loop through the array...


"r wilcox" wrote in message
...
I'm trying to load an array into a list box. When I execute the following
macro, it tells me that I have a subscript out of range for the AddItem
code.

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Sheet1Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
UserForm1.Show
End Sub

Loading array into list box
 

The range should be A1:D4. How do I create a 2 dimensional range?

"Brian" wrote:

Just use

UserForm1.ListBox1.List = MyArray

no need to loop through the array...


"r wilcox" wrote in message
...
I'm trying to load an array into a list box. When I execute the following
macro, it tells me that I have a subscript out of range for the AddItem
code.

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Sheet1Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
UserForm1.Show
End Sub

Loading array into list box
 

You either use a multi-column listbox and load it as 2D, or loop through the
cells and load as in your example.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"r wilcox" wrote in message
...
The range should be A1:D4. How do I create a 2 dimensional range?

"Brian" wrote:

Just use

UserForm1.ListBox1.List = MyArray

no need to loop through the array...


"r wilcox" wrote in message
...
I'm trying to load an array into a list box. When I execute the
following
macro, it tells me that I have a subscript out of range for the
AddItem
code.

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Sheet1Range("A1:A4").Value
For Ctr = LBound(MyArray) To UBound(MyArray)
UserForm1.ListBox1.AddItem MyArray(Ctr)
Next
UserForm1.Show
End Sub