Listbox Problem

[Nick147]

Below is my code for listbox. My problem is that the listbox pulls information from the activesheet not the "CreditData" sheet. What is wrong with my code?

colcnt = Worksheets("CreditData").UsedRange.Columns.Count
rowcnt = Worksheets("CreditData").UsedRange.Rows.Count
Set rng = Worksheets("CreditData").Rows("2:" & rowcnt)
With ListBox2
.ColumnCount = 5
.RowSource = rng.Address
cw = ""
For c = 1 To .ColumnCount
cw = cw & rng.Columns(c).Width & ";"
Next c
.ColumnWidths = cw
.ListIndex = 0
End With

NL

 

[Greg Truby]

It's because rng.Address is not returning more specific information. Try setting the External arg to true.

.RowSource = rng.Address(External:=True)

Does that fix it?

 

[Nick147]

That worked.

Thanks for you help Greg.

NL

 

[Greg Truby]

One footnote: the External address also adds in the workbook name. If the workbook name will vary, you might keep that in mind as a possible issue. If it causes problems, you may want to re-work the code to clip off the WB name from the address before assigning it to the .RowSource property.

 

[Nick147]

It shouldn't be a problem this time, but thank you for the additional information.

NL