Listbox rowsource on Userform

[Wendy]

Hi

I'm trying to set the rowsource of a listbox, I keep getting subscript out
of range error.

Thanks

Wendy

Private Sub UserForm_Initialize()
Dim LastRowNo As Long
Dim LastRow as String
Set ws = Workbooks("post.xls").Worksheets("data")


'find first empty row in sheet

LastRowNo = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
LastRow = "A2:C" & LastRowNo

Me.ListBox1.RowSource = LastRow

End Sub

 

[Jim Cone]

Wendy,
Some suggestions...
Declare ws as a worksheet
Prefix "Rows.Count" with ws... ws.RowsCount
Verify that the workbook name and sheet name are correct.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Wendy"
wrote in message
Hi
I'm trying to set the rowsource of a listbox, I keep getting subscript out
of range error.
Thanks
Wendy

Private Sub UserForm_Initialize()
Dim LastRowNo As Long
Dim LastRow as String
Set ws = Workbooks("post.xls").Worksheets("data")


'find first empty row in sheet

LastRowNo = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
LastRow = "A2:C" & LastRowNo

Me.ListBox1.RowSource = LastRow

End Sub

 

[FSt1]

hi
your script out of range seems to occur on this line
Set ws = Workbooks("post.xls").Worksheets("data")
try changing it to....
Set ws = Workbooks("post").Worksheets("data")
ie without the .xls

Regards
FSt1

 

[Dave Peterson]

I'd use:

Option Explicit 'forces you to declare your variables
Private Sub UserForm_Initialize()

Dim myRng As Range
Dim LastRow As Long

'With Workbooks("post.xls").Worksheets("data")
'or if the data was in the same workbook as the userform
'With ThisWorkbook.Worksheets("data")
'I didn't include the extra blank row!
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("A2:C" & LastRow)
End With

With Me.ListBox1
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
End With

End Sub

 

[Dave Peterson]

Dropping the .xls may break the code depending on a Windows setting for each
user. It's always safer to include the extension.

 

[Wendy]

Hi

I can only get Dave's solution to populate the form if the worksheet "data"
is in the same workbook as the form and post.xls is actually in
D:\Temp\Post.xls

Wendy

 

[Dave Peterson]

I'm not sure I understand.

Is your data in the same workbook as the userform? Or is in the Post.xls
workbook?

If it's in the post.xls workbook, then you have to make sure that workbook is
open first.

Option Explicit 'forces you to declare your variables
Private Sub UserForm_Initialize()

Dim myRng As Range
Dim LastRow As Long
dim Wkbk as workbook

set wkbk = nothing
on error resume next
set wkbk = workbooks("Post.xls")
on error goto 0

if wkbk is nothing then
'it's not open, so open it
set wkbk = workbooks.open(filename:="D:\Temp\Post.xls")
end if

With wkbk.worksheets("data")
'I didn't include the extra blank row!
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("A2:C" & LastRow)
End With

With Me.ListBox1
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
End With

End Sub

========
As an aside, if the d:\temp folder is used as the windows temp folder, then it's
really not a good idea to store any important file in that folder.