How do I pass an array to a listbox?

[Titus A Ducksass - AKA broken-record]

I have a variant array "class" which I wish to pass to a list box on
another form.

Please advise.

--
Apachie,
Photoshop,
Flash
Dreamweaver
MySql
PHP
Training videos : www.wz2k.co.uk

[Chip Pearson]

Could you perhaps provide a few more details about exactly what
you are trying to do?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Titus A Ducksass - AKA broken-record" <me@neverumind.com> wrote
in message news:1c2141lvguhbaajt36lbs7cjea5td331pn@4ax.com...
>I have a variant array "class" which I wish to pass to a list
>box on
> another form.
>
> Please advise.
>
> --
> Apachie,
> Photoshop,
> Flash
> Dreamweaver
> MySql
> PHP
> Training videos : www.wz2k.co.uk

[Alasdair Stirling]

I assume that yu have a list of names that you want to transfer to a listbox.
Assuming that your list box is on a UserForm the following will work:

Private Sub UserForm_Initialize()
' Declare the array (for three names)
Dim MyArray(3) As String
Dim r As Integer
' Fill the array
MyArray(1) = "Colin"
MyArray(2) = "Bill"
MyArray(3) = "Fred"
' Loop through the array names
' and spit into the list box
For r = 1 To UBound(MyArray, 1)
' Assumes that the list box
' name is ListBox1
ListBox1.AddItem MyArray(r)
Next r
End Sub

Regards,

Alasdair Stirling

"Titus A Ducksass - AKA broken-record" wrote:

> I have a variant array "class" which I wish to pass to a list box on
> another form.
>
> Please advise.
>
> --
> Apachie,
> Photoshop,
> Flash
> Dreamweaver
> MySql
> PHP
> Training videos : www.wz2k.co.uk
>

[Titus A Ducksass - AKA broken-record]

On Tue, 22 Mar 2005 15:13:22 -0600, "Chip Pearson" <chip@cpearson.com>
wrote:

>Could you perhaps provide a few more details about exactly what
>you are trying to do?

I am trying to read in a worksheet into the variant array which then
should be made available from a list box
I can reference every element of the array but cannot figure out how
to pass it to the list box. In C I would pass the address of the
array but in excel, I cannot figure it out.

Sample data in the sheet is:'

Class Date Time
Dogs 08/04/2005 15:00
Dogs 09/04/2005 19:00
Dogs 09/04/2005 15:00
Cats & Dogs 10/04/2005 15:00
Cats & Dogs 16/04/2005 19:00
Cats & Dogs 16/04/2005 15:00
Cats 27/03/2005 15:00
Cats 27/03/2005 19:00

There are c.136 records and I expect it to change by 20 up and down
but will remain roughly 136 which is why I am using the variant array
as the exact number will not be known at runtime.


--
Apachie,
Photoshop,
Flash
Dreamweaver
MySql
PHP
Training videos : www.wz2k.co.uk

[Titus A Ducksass - AKA broken-record]

On Tue, 22 Mar 2005 15:31:05 -0800, "Alasdair Stirling"
<AlasdairStirling@discussions.microsoft.com> wrote:

>I assume that yu have a list of names that you want to transfer to a listbox.
> Assuming that your list box is on a UserForm the following will work:
>
>Private Sub UserForm_Initialize()
> ' Declare the array (for three names)
> Dim MyArray(3) As String
> Dim r As Integer
> ' Fill the array
> MyArray(1) = "Colin"
> MyArray(2) = "Bill"
> MyArray(3) = "Fred"
> ' Loop through the array names
> ' and spit into the list box
> For r = 1 To UBound(MyArray, 1)
> ' Assumes that the list box
> ' name is ListBox1
> ListBox1.AddItem MyArray(r)
> Next r
>End Sub
>
>Regards,
>Alasdair Stirling

Thanks, I will look at that - there are too many records to
permanently store in the array so I will load and save them in the
sheet as appropriate.
Thanks again.
Derek

--
Apachie,
Photoshop,
Flash
Dreamweaver
MySql
PHP
Training videos : www.wz2k.co.uk