Solved: Add array items to multi column ListBox

[Glaswegian]

Hi all

Stuck again!

I have information going to a hidden sheet via an input form. The information goes to columns A to L inclusive. Col A is simply a counter that increments by 1 each time data is input. So far so good. I'm now trying to retrieve some of that data and drop it into a listbox. I want to bring 3 columns to the listbox, A, E and H. The number of rows of data in the sheet will increase over time.

The listbox will therefore show a brief summary of records previously input - the three columns are the counter, customer name, and one other piece of info. Users will be able to click on a row and retrieve the full record (that bit I can do fine).

I can't work out how to load the relevant info into an array and then populate the listbox! (arrays were never something I fully understood.) My sad efforts so far amount to this

Dim x As Long
Dim myArray()
For x = 1 To Sheets("Records").Range("A2", Range("A65536").End(xlUp)).Row
    myArray(x, 0) = x
Next x
   frmSelectRecord.lbxRecords.List() = myArray

but it's not really much.

Any help greatly appreciated.

Thanks.

[lucas]

See if this helps...
http://vbaexpress.com/kb/getarticle.php?kb_id=600

Look for this comment in the code:
'Define the list and where it's obtained from (Columns A, D, G in this example)

[Glaswegian]

Hi Lucas

Thanks for that - I'll have play with it and let you know if I have any problems. Hope not - I get the general idea.

Thanks again.

[Glaswegian]

Hi again lucas

I'm a bit stuck with this (I hate arrays!!). This is what I have so far

Dim x As Long
Dim R As Integer
Dim myArray()
 
x = Sheets("Records").Range("A2", Range("A65536").End(xlUp)).Row
ReDim myArray(x, 3)
 
With Sheets("Records")
For R = 0 To x
    myArray(R, 0) = .Range("A" & x)
    myArray(R, 1) = .Range("D" & x)
    myArray(R, 2) = .Range("G" & x)
Next R
End With

but I get a Subscript out of range error, whcih I'm guessing is something to do with the array.

Any help greatly appreciated.

[Charlize]

There isn't a row 0 (zero) in excel. They start at 1.[vba]

Dim x As Long
Dim R As long
Dim Pos as long
Dim myArray()
x = Sheets("Records").Range("A" & rows.count).End(xlUp).Row
'because you start at 0 in the array the no of rows aren't
'equal to no of rows in array. since x is the no of times the loop
'will be executed.
'position in sheet
x = x - 1
ReDim myArray(x, 3)
pos = 1

With Sheets("Records")

For R = 0 To x

myArray(R, 0) = .Range("A" & pos)
myArray(R, 1) = .Range("D" & pos)
myArray(R, 2) = .Range("G" & pos)
pos = pos+1
Next R
End With

[/vba]

[mikerickson]

This is a different approach. All of the data in A:H is in the list box, but only columns A,E,H are shown.

Dim dataRRay As Variant

dataRRay = Range(Range("a1"), Range("h65536").End(xlUp)).Value
With UserForm1.ListBox1
    .ColumnCount = 8
    .ColumnWidths = ";0;0;0;;0;0;;"
    .List = dataRRay
End With

[lucas]

Charlize's solution works perfectly for me....Mike I didn't try your method yet but will get to it...looks interesting.

[Glaswegian]

Thanks for all your suggestions folks and apologies for not replying sooner - work managed to get in the way.

My own fault at the end of the day - I had switched to Option Base 1 - and forgot I had done that.

I'll mark this one solved.