bryVA
12-16-2009, 12:20 PM
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
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