reference sheets
 

I am using a listbox in sheet2, I want to refernce a range in sheet1,
have the following statement:

Private Sub ListBox1_Click()

ListBox1.ListFillRange = Worksheets("Sheet1").Range("A4:A14").Address

End Sub


For some reason it is not showing any values. Anyone know why?
Thanks

--
Message posted from http://www.ExcelForum.com

reference sheets
 

MaxRott

It's because the Address property of a range does not identify which sheet
it's on. These two line produce the exact same result

Sheets("Sheet1").Range("A1").Address
Sheets("Sheet2").Range("A1").Address

If you know the sheet and the range, then just assign a string to the
ListFillRange

ListBox1.ListFillRange = "Sheet1!A4:A14"

If you want to use address, then you it would look like this

ListBox1.ListFillRange = Worksheets("Sheet1").Name & "!" &
Worksheets("Sheet1").Range("A4:A14").Address

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"MaxRott" wrote in message
...
Forgot to mention.....when I use data on the same sheet I can use:

Private Sub ListBox1_Click()

ListBox1.ListFillRange = ActiveSheet.Range("A4:A14").Address

End Sub

and it works fine.


---
Message posted from http://www.ExcelForum.com/