populating a listbox from an array

  • Thread starter Thread starter Graham Whitehead
  • Start date Start date
G

Graham Whitehead

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
 
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
 
Back
Top