populating a listbox from an array

Hi, I know there must be a fairly simple solution to this one but I cant
quite get the syntax right. Basically I have a set of values stored in an
array an I simply want to populate the list box with these. one article on
the microsoft website simply gave:

VBA:
'Assign the array to the listbox
ListBox1.List = LArray However, I get an object required error. Can anyone
help me out? Thanks.

Graham,
All these work for me:
Private Sub CommandButton1_Click()
Dim MyArray As Variant
Dim StrArray(9) As String
Dim i As Long

Const MyValues As String = "Val 1,Val 2,Val 3,Val 4,Val 5"

With ListBox1
.Clear
.List = Split(MyValues, ",")
.ListIndex = 0

MyArray = Split(MyValues, ",")
.Clear
.List = MyArray
.ListIndex = 0

For i = 0 To 9
StrArray(i) = "String array " & i
Next
.Clear
.List = StrArray
.ListIndex = 0
End With
End Sub

NickHK

"Graham Whitehead" <gwhitehead77@hotmail.com> wrote in message
news:OOWetPhtGHA.324@TK2MSFTNGP06.phx.gbl...
> Hi, I know there must be a fairly simple solution to this one but I cant
> quite get the syntax right. Basically I have a set of values stored in an
> array an I simply want to populate the list box with these. one article on
> the microsoft website simply gave:
>
> VBA:
> 'Assign the array to the listbox
> ListBox1.List = LArray However, I get an object required error. Can anyone
> help me out? Thanks.
>
>

Is this list in a userform or on a worksheet?

Assigning the list to an array directly works, but if you are assigning
this to a listbox on a worksheet you must first specify the worksheet
since control objects created on a sheet is specific to that sheet
only. Code would look like:

Worksheets("Sheet1").ListBox1.List = LArray

As soon at you type "." after the Worksheets("Sheet1"), you would see
the listbox name in the properties for the sheet in the dropdown, if
you have your vba to show properties for objects.

Hope that helps



NickHK wrote:
> Graham,
> All these work for me:
> Private Sub CommandButton1_Click()
> Dim MyArray As Variant
> Dim StrArray(9) As String
> Dim i As Long
>
> Const MyValues As String = "Val 1,Val 2,Val 3,Val 4,Val 5"
>
> With ListBox1
> .Clear
> .List = Split(MyValues, ",")
> .ListIndex = 0
>
> MyArray = Split(MyValues, ",")
> .Clear
> .List = MyArray
> .ListIndex = 0
>
> For i = 0 To 9
> StrArray(i) = "String array " & i
> Next
> .Clear
> .List = StrArray
> .ListIndex = 0
> End With
> End Sub
>
> NickHK
>
> "Graham Whitehead" <gwhitehead77@hotmail.com> wrote in message
> news:OOWetPhtGHA.324@TK2MSFTNGP06.phx.gbl...
> > Hi, I know there must be a fairly simple solution to this one but I cant
> > quite get the syntax right. Basically I have a set of values stored in an
> > array an I simply want to populate the list box with these. one article on
> > the microsoft website simply gave:
> >
> > VBA:
> > 'Assign the array to the listbox
> > ListBox1.List = LArray However, I get an object required error. Can anyone
> > help me out? Thanks.
> >
> >