View Full Version : Listbox RowSource Requires Range or String
brorick
05-27-2008, 10:57 PM
I have a userform with fields that uses a range on Sheet1. The listbox on the userform refers to a range on another sheet (Sheet2). I know if the listbox referred to data on Sheet1 I could use the following code with no issues.
Dim rTable As Range
Set rTable = Range(Cells(1, "AA"), Cells(1, "AV").End(xlDown))
Set rTable = rTable.Resize(rTable.Rows.Count - 1).Offset(1)
lstEmpList.RowSource = rTable.Address
When the range is on another worksheet I learned I have to use a string instead. My dilema is I need to mirror the use of xlDown and the offset references I used in the code above. Can anyone show me how to modify the following code to look like the code above.
Dim MyStr As String
MyStr = "frmAdmin!AJ1:AM2"
lstEmpList.RowSource = MyStr
I originally tried to get this code to work but I had no success. :doh:
Dim rng As Range
Set rng = Worksheets("frmAdmin").Range("DocInfo").End(xlDown).Value
lstEmpList.RowSource = rng.Resize(rng.Rows.Count - 1).Offset(1).Address
Bob Phillips
05-28-2008, 01:19 AM
Try
lstEmpList.RowSource = rng.Resize(rng.Rows.Count - 1).Offset(1).Address(,,,True)
brorick
05-28-2008, 06:12 PM
XLD, thank you for the response. For some reason I am unable to get it to work. Could you verify if this is the whole piece of code. Thanks.
Dim rng As Range
Set rng = Worksheets("frmAdmin").Range("DocInfo").End(xlDown).Value
lstEmpList.RowSource = rng.Resize(rng.Rows.Count - 1).Offset(1).Address(,,,True)
brorick
05-31-2008, 04:46 PM
I am up against a deadline and I hope someone has a solution. This issue has my project at a complete stand still. :doh: Here is a scenario to hopefully shed some light on my issue.
My userform defaults to the worksheet "frmMain". The rowsource of the listbox(lstEmpList) on my userform is based on the range name "ProdList" which is located on the worksheet "frmAdmin". The code I attempted to use is this code, but it does not work. :help
The range "ProdList" is based on cells AA1:AB3.
AA1---------------AB3
Item--------------Cost
Pie----------------8.00
Cake--------------7.00
Dim rng As Range
Set rng = Worksheets("frmAdmin").Range("ProdList").End(xlDown).Value
lstEmpList.RowSource = rng.Resize(rng.Rows.Count - 1).Offset(1).Address(,,,True)
Does anyone have any ideas why? Thanks in advance. : pray2:
mdmackillop
06-01-2008, 01:53 AM
Have a look at this simplified sample. You are best to qualify with each sheet name used as source data and then the userform can be opened from any location. Using the List method allows you to access the range data without requiring a text address with sheet reference.
Option Explicit
Private Sub UserForm_Initialize()
Dim rTable As Range
'Listbox1
With Sheets("Sheet1")
Set rTable = Range(.Cells(1, "A"), .Cells(1, "F").End(xlDown))
Set rTable = rTable.Resize(rTable.Rows.Count - 1).Offset(1)
End With
lstEmpList.RowSource = "=Sheet1!" & rTable.Address
'or
'lstEmpList.List = rTable.Value
'Listbox2
With Sheets("Sheet2")
Set rTable = Range(.Cells(1, "C"), .Cells(1, "H").End(xlDown))
Set rTable = rTable.Resize(rTable.Rows.Count - 1).Offset(1)
End With
lstTest.RowSource = "=Sheet2!" & rTable.Address
'or
'lstTest.List = rTable.Value
End Sub
Bob Phillips
06-01-2008, 02:13 AM
I think this does what you want
Dim rng As Range
Set rng = Worksheets("frmAdmin").Range("DocInfo")
lstEmpList.RowSource = rng.Offset(1, 1).Resize(rng.End(xlDown).Row - 1).Address(, , , True)
brorick
06-01-2008, 07:56 PM
Mdmackillop and xld thank you for your help. I will give them both a try.
mdmackillop
06-01-2008, 11:57 PM
BTW, Value should be omitted in this context as Rng is a Range variable.
Dim rng As Range
Set rng = Worksheets("frmAdmin").Range("DocInfo").End(xlDown).Value
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.