Listbox RowSource Requires Range or String

[brorick]

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.

[VBA]
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[/VBA]

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.

[VBA]
Dim MyStr As String

MyStr = "frmAdmin!AJ1:AM2"
lstEmpList.RowSource = MyStr
[/VBA]

I originally tried to get this code to work but I had no success.

[VBA]
Dim rng As Range

Set rng = Worksheets("frmAdmin").Range("DocInfo").End(xlDown).Value

lstEmpList.RowSource = rng.Resize(rng.Rows.Count - 1).Offset(1).Address

[/VBA]

[Bob Phillips]

Try

[vba]

lstEmpList.RowSource = rng.Resize(rng.Rows.Count - 1).Offset(1).Address(,,,True)
[/vba]

[brorick]

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.

[vba]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)
[/vba]

[brorick]

I am up against a deadline and I hope someone has a solution. This issue has my project at a complete stand still. 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.

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.

[mdmackillop]

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.


[vba]
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

[/vba]

[Bob Phillips]

I think this does what you want

[vba]

Dim rng As Range

Set rng = Worksheets("frmAdmin").Range("DocInfo")

lstEmpList.RowSource = rng.Offset(1, 1).Resize(rng.End(xlDown).Row - 1).Address(, , , True)
[/vba]

[brorick]

Mdmackillop and xld thank you for your help. I will give them both a try.

[mdmackillop]

BTW, Value should be omitted in this context as Rng is a Range variable.
[VBA]Dim rng As Range
Set rng = Worksheets("frmAdmin").Range("DocInfo").End(xlDown).Value [/VBA]