Code for listbox.rowsource?

[Todd_M]

Private Sub CommandButton1_Click()

For Each s In Worksheets("sheet2").range("a1:a100")
If s.Value = ListBox1.Value Then
x = s.Offset(0, 1).Value
Sheets(x).Activate

ListBox2.rowsource = Sheets(x).range("a1:i10000")

End If
Next s
exit sub

This code finds a sheet from a selection in listbox1 when the user clicks the commandbutton. Why cant I figure out why I cant get the range A1:I10000 of that sheet to display in listbox2 ????
Please help

 

[Aladin Akyurek]

Todd,

Off topic, more or less anyway.

A ListBox housing 10,000 items amazes me. How do you use it?

Aladin

 

[Todd_M]

Its actually supposed to read range(a1:i100).
Any ideas?

 

[chedarmaster]

I really would like that someone had replied to you, because I'm looking for the exact same thing

Will continue to search the forum

 

[VoG]

Try like this

UserForm1.ListBox1.RowSource = Worksheets("sheet1").Range("A1:I100").Address(external:=True)

 

[chedarmaster]

Wow!



Thanks very much!

 

[Norie]

You can also use List.

Listbox1.List = Sheets(x).Range("A1:I100").Value

No real advantage I suppose.

Doesn't link the range and listbox directly.

 

[chedarmaster]

Thanks!

So, if I use list, when the data changes in the sheet, it will not change in the ListBox? That has some advantages for what I'm trying to do.

 

[Norie]

That's right.

The obvious disadvantage is that you'll need to take care of the transfer to the worksheet yourself.

Mind you that could be an advantage as it'll give you more control.