reference sheets

  • Thread starter Thread starter MaxRott
  • Start date Start date
M

MaxRott

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
 
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
 
Back
Top