Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
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 "Wendy" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
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 Wendy wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
Dropping the .xls may break the code depending on a Windows setting for each
user. It's always safer to include the extension. FSt1 wrote: 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 "Wendy" wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
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" wrote in message ... 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 Wendy wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Listbox rowsource on Userform
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. Wendy wrote: 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" wrote in message ... 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 Wendy wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox in UserForm | Excel Discussion (Misc queries) | |||
How to load a listbox in a userform? | Excel Discussion (Misc queries) | |||
???Help??? Userform.Listbox.rowsource = ??? | Excel Discussion (Misc queries) | |||
UserForm Listbox issue | Excel Discussion (Misc queries) | |||
UserForm Listbox in VBC | Excel Discussion (Misc queries) |