How to pass array to listbox from a procedure

[Alumicarus]

Hi again,

still working on this list box problem.
I have some code that has asked a user a series of questions and populated a two dimensional array. I need my code at some point to automatically call a listbox up to display the contents of the array.

I am able to pass the array from one procedure to another, but i cannot seem to send it to my userform.

In the following example i am using some code i grabbed from a book to populate a similar 2 dim array.

' this part is just to Fill the list box variable "DATA"

Dim data(1 To 12, 1 To 2) As Variant
For i = 1 To 12
data(i, 1) = Format(DateSerial(2001, i, 1), "mmmm")
Next i
For i = 1 To 12
data(i, 2) = Day(DateSerial(2001, i + 1, 1) - 1)
Next i


'this will call up txtlistboxarray - but it will be empty
txtListboxarray.Show

'I tried this but it does not work
'txtListboxarray.Show (data)

'just for a sanity check i tried to pass to another procedure "mycall" 
'the contents of data were successfully passed
Call mycall(data)

End Sub
'the following is the code from my userform txtlistboxarray
'again this comes up but "data" does not appear to be available to populate
'the listbox
--------------------------------------------------------------------------------
Sub txtListBoxarray_Click(data, mypassedvariable)

txtListboxarray.ColumnCount = 2
txtListboxarray.List = data


End Sub
------------------------------------------------------------------------------

Private Sub UserForm_Click()

End Sub

Any ideas?

thanks
Ashley

 

[Norie]

Where have you declared data?

Perhaps you should declare it as a public variable.

By the way why do you have the code to populate the array on it's own, can't you make it part on the Initialize event of the userform.

Generally I would think that code to populate a control on a userform would best be in tha t event.

 

[parry]

Hi, heres how to populate a listbox with an array - make sure the listbox columncount property is 2. Also I dont think you need 2 for/next loops

Sub Example()
Dim data(1 To 12, 1 To 2) As Variant, i As Integer

For i = 1 To 12
    data(i, 1) = Format(DateSerial(2001, i, 1), "mmmm")
    data(i, 2) = Day(DateSerial(2001, i + 1, 1) - 1)
Next i

'Ensure that the column count property of the listbox = 2
'or else populating array wont work because it's 2 dimensional
Sheet1.ListBox1.List = data

End Sub

 

[Alumicarus]

Hi, Thankyou for the help,
This is where I stand:



This was the code you provided:

Sub Example() 
Dim data(1 To 12, 1 To 2) As Variant, i As Integer 

For i = 1 To 12 
    data(i, 1) = Format(DateSerial(2001, i, 1), "mmmm") 
    data(i, 2) = Day(DateSerial(2001, i + 1, 1) - 1) 
Next i 

'Ensure that the column count property of the listbox = 2 
'or else populating array wont work because it's 2 dimensional 
Sheet1.ListBox1.List = data 

End Sub

This was the code as i modified:

Sub Example()
Dim DATA(1 To 12, 1 To 2) As Variant, i As Integer

For i = 1 To 12
    DATA(i, 1) = Format(DateSerial(2001, i, 1), "mmmm")
    DATA(i, 2) = Day(DateSerial(2001, i + 1, 1) - 1)
Next i

'Ensure that the column count property of the listbox = 2
'or else populating array wont work because it's 2 dimensional


'THIS WAS ORRIGINAL -->Sheet1.ListBox1.List = data
'I TRIED THIS--> Sheet1.txtListboxarray.List = data
txtListboxarray.Show = DATA '<---- THIS WORKS BUT ONLY IF "DATA" IS REMMED OUT
'(AND OF COURSE LISTBOX IS BLANK)
'IF I UN-REM THE "=DATA" PART IT GIVES ME ERROR THAT SAYS "ASSIGNMENT TO CONSTANT NOT PERMITTED"
'WITH ".SHOW=" HIGHLIGHTED


End Sub

a question was also asked why i do not populate the array in the userform.
I can do this, and when i do it works fine. The problem is that in this example i am using a short little loop to populate and array. In my actual program the process of populating the array takes up most of my code.
do i need to put all the code in the "user form" instead of the module?
My understanding of this is that I can have code in a module that calls to a userform and pops up a listbox that sumerizes my array. If that is not possible please advise..

Thanks again everyone for your help.
regards,
Ashley