PDA

View Full Version : Solved: Add array items to multi column ListBox



Glaswegian
06-26-2007, 06:50 AM
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
06-26-2007, 11:01 AM
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
06-27-2007, 04:51 AM
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
06-28-2007, 03:10 AM
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
06-28-2007, 04:52 AM
There isn't a row 0 (zero) in excel. They start at 1.

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

mikerickson
06-28-2007, 06:06 AM
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
06-28-2007, 06:33 AM
Charlize's solution works perfectly for me....Mike I didn't try your method yet but will get to it...looks interesting.

Glaswegian
07-02-2007, 01:38 AM
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.