Try changing this line

sTest = "C2:" & strLastRow

to

sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Henry" wrote in message
...
I have a multi-column listbox on sheet one of an Excel 2003 workbook.
I'm importing two .CSV files on pages two and three of thw workbook,
respectively.

My boss wants to have the sheet load with the data already visible and
accessible, so he vetoed using a UserForm.

From sheet three, I'm selecting a range and want to populate the
listbox on sheet three. I've got the correct range selected, but when I
try to populate the listbox, it comes up blank. I can't seem to get the
data to display in the listbox. When I step through the code with the
debugger, I can see that the LISTCOUNT is correct, but still, you can't
see the results. I'm attaching the code below. Too bad that the
RowSource propert is not available in this case... The code is from a
command button I added in order to test the code, so I would have
problems wrestling with the workbook's OPEN event.

Any clues to this mysterious (to me, at least) behavior would be greatly
appreciated.


Henry
DPM Mellon



= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =


Private Sub cmdPopulateListBox_Click()

Dim MyRange As Variant
Dim DestRange As Range

Dim lnFoo

Application.ScreenUpdating = False
Sheets(3).Activate

intLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
strLastRow = "F" & Trim(Str(intLastRow))

sTest = "C2:" & strLastRow

Sheets(3).Range(sTest).Select

lnFoo = MsgBox("MyRange = " & sTest, vbOKOnly, "Test")
MsgBox "The selection object type is " & TypeName(Selection)

Sheets(1).Activate
ListBox1.Activate
ListBox1.Locked = False
ListBox1.ListFillRange = ""
ListBox1.ListFillRange = sTest
ListBox1.Locked = True

Application.ScreenUpdating = True

End Sub