Code for listbox.rowsource?

Todd_M

Board Regular
Joined
Feb 24, 2002
Messages
117
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I really would like that someone had replied to you, because I'm looking for the exact same thing :LOL:

Will continue to search the forum
 
Upvote 0
Try like this

Code:
UserForm1.ListBox1.RowSource = Worksheets("sheet1").Range("A1:I100").Address(external:=True)
 
Upvote 0
You can also use List.
Code:
Listbox1.List = Sheets(x).Range("A1:I100").Value
No real advantage I suppose. :)

Doesn't link the range and listbox directly.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top