Filling a List Box in Excel from an Array

[Guest]

Hope someone can help. I have a listbox in excel which I am trying to
populate from an array. The array is picking up certain values from a sheet
depending on a previous value selected in another list box. I currently have
the array code in a module.

regards,
Martin

 

[Bob Phillips]

As an example

For i = LBound(ary) To UBound(ary)
Listbox1.AddItem ary(i)
Next i

--

HTH

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

 

[Andy Pope]

Another example.

Dim strArray(2) As String

strArray(0) = "X"
strArray(1) = "Y"
strArray(2) = "Z"
ListBox1.List = strArray

Cheers
Andy

 

[Guest]

Hi Bob,

I have done the below but having read other threads is it not possible to
create a dropdown in a worksheet using the control box and then add items
using VBA?? I am having to reference the control as follows

worksheets("Main").shapes("List Box 2").AddItem UniArray(transnumber)

I get an 'Object does not support property or method' runtime error. Is the
only way around this to create a dropdown via code?

 

[Bob Phillips]

Martin,

If you added the control from the controls toolbox, you need to use

worksheets("Main").ListBox2.AddItem UniArray(transnumber)

or

worksheets("Main").oleobjects("ListBox2").object.AddItem
UniArray(transnumber)



--

HTH

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