Listbox Row Source

[bryVA]

Hello,

I have a userform with a listbox on it and I want it to dynamically fill in the list box based on a combobox selection. The following code is what I have so far.

Private Sub ComboBox1_Change()
Dim iRng As Range
Dim AgName As String
AgName = Me.ComboBox1.Value

With Sheets("Downtime").Range("SearchName")

Set iRng = .Find(What:=AgName, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
iRng = iRng.Offset(1, 1).Range("A2:D")

'Set properties of listbox2

With Me.ListBox2
.BoundColumn = 1
.ColumnCount = 4
.ColumnHeads = True
.TextColumn = True
.RowSource = iRng & xlLastRow("Downtime")
.ListStyle = fmListStyleOption
.ListIndex = 0
End With
End Sub

My variable iRng errors out. How can I get the list box to get the data based on what was selected in the combobox? The sheet is setup with the name of the person and the in the next four columns over is the information I need in the list box. Can anyone help me figure this out?

THank you all,

-B

[Bob Phillips]

This line


iRng = iRng.Offset(1, 1).Range("A2:D")


shout be Set iRng as it is an object, but what exactly is it supposed to do, this bit .Range("A2:D") is invalid.

[bryVA]

I am trying to get the iRng the range I want showing in the listbox. So If I have bob in combobox1 the code will find bob in the sheet and take the next four columns next to bob all the way to the last row and input this into the listbox. Does this makes sense? So if I want Doug then it finds in column J1 doug and it take "K1:N" & lastrow and insert this into the row source.

Thanks for your help,

-B

[mdmackillop]

Private Sub ComboBox1_Change()
Dim iRng As Range
Dim AgName As String
AgName = Me.ComboBox1.Value

With Sheets("Downtime").Range("SearchName")

Set iRng = .Find(What:=AgName, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
Set iRng = iRng.Offset(1, 1).Range(Cells(2, 1), LRw("D"))

'Set properties of listbox2

With Me.ListBox2
.BoundColumn = 1
.ColumnCount = 4
.ColumnHeads = True
.TextColumn = True
.RowSource = iRng
.ListStyle = fmListStyleOption
.ListIndex = 0
End With
End Sub

Function LRw(Col As Variant) As Range
Set LRw = Cells(Rows.Count, Col).End(xlUp)
End Function