fill a listbox with data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I would like to fill a list box with a click on a commandbutton.
The code I use is printed below.

----------------------------
Private Sub CommandButton3_Click()
' De listboxen vullen met de juiste data

ListBox1.RowSource = "A2:D8"
ListBox1.ColumnCount = 4
ListBox1.ColumnWidths = "360;25;50;50"

ListBox2.RowSource = "A2:D10"
ListBox2.ColumnCount = 4
ListBox2.ColumnWidths = "360;25;50;50"

ListBox3.RowSource = "A2:D14"
ListBox3.ColumnCount = 4
ListBox3.ColumnWidths = "360;25;50;50"

ListBox4.RowSource = "A2:D18"
ListBox4.ColumnCount = 4
ListBox4.ColumnWidths = "360;25;50;50"

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

However, I would like to use more than one sheet..
and the data selected with this code is only the data from sheet1.

How can I specify the sheet where the data has to be collected from?
thank you all.. :)

Arjan Bregman


*****
the knowledge is always there, maybe hidden, but it is there..
*****
 
I ran into this problem a few weeks ago.

To be able to add items to a listbox, you need to use the additem method....
listbox1.additem "item1"

but because you've specified the rowsource of the listbox, this makes it
static and thus you can't add items to it.

What you need to do is set up a loop to add your items to the list box via
the additem method and then use the additem method in a button click event.

eg. This will add everything from A2 to A8 to a listbox
For MyLoop=2 to 8
ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value
Next MyLoop

I'm not sure off hand how to do the columns, but the help files should be
able to tell you that.

Oh and if you want to specify a sheet name in the rowsource property, I
think it goes something like...
ListBox4.RowSource = "Sheet1!A2:D8"

using a ! to separate the sheetname from the range.
 
Neily,

thnx! for your effort..
The listbox is only a view tool for the user, therefore there is no problem
that it is static.. (Am I using the wrong tool here?)

the code does works fine, because I would also like to use the columnheads..
However, when I use ListBox4.RowSource = "Sheet1!A2:D8" i get an error (nr
380)

any idea?






--
Arjan Bregman

*****
the knowledge is always there, maybe hidden, but it is there..
*****
 
Using RowSource is the only way to get column heads.

ListBox4.RowSource = "Sheet1!A2:D8" should work fine

What is the text of error 380?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

I used a space in my sheetname. Is this not allowed?
Because, when I used an other sheetname, without a space it works fine!

However, problem solved.. thnx!!



--
Arjan Bregman

*****
the knowledge is always there, maybe hidden, but it is there..
*****
 
I see, in that case use something like

ListBox1.RowSource = "'Sheet 2'!A2:D8"

note the single quotes around the sheet name as well as double quotes around
the whole.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
I like to let excel worry about the syntax.

I'd use:

ListBox4.RowSource _
= worksheets("Sheet1").range("A2:D8").address(external:=true)

or

ListBox4.RowSource _
= worksheets("Sheet 2").range("A2:D8").address(external:=true)
 
Back
Top