I like to let excel worry about the syntax.

I'd use:

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

or

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



Arjan wrote:

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..
*****

"Bob Phillips" wrote:

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)

"Arjan" wrote in message
...
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..
*****


"Neily" wrote:

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.



"Arjan" wrote:

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..
*****




--

Dave Peterson